Exporting to MDB

Postby NorwalkUMC » Sat Apr 02, 2011 2:27 pm

When exporting from the Utility Menu to a MDB file, the Access file, when opened in Access 2007 does not include numeric values out to the penny, but seem to round off to the nearest whole dollar.

How can we export to a MDB Access file and capture all of the numeric values including cents?

Thanks,
Dennis
NorwalkUMC
 
Posts: 15
Joined: Sun Feb 07, 2010 4:07 pm

Re: Exporting to MDB

Postby NeilZ » Sat Apr 02, 2011 6:10 pm

It may be an issue with the way the data is being exported from a Foxpro database to the Access database.

I did a comparison between the FASTRD table design in FoxPro, with the way the design was exported in Access. The AMOUNT column in FoxPro is 11.2 (11 numbers with 2 decimal places), in Access the design is 11 numbers but the decimal is set to 'auto' on the table design.

May have been a inadvertent bug in the design ?? :?:

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 8906
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Exporting to MDB

Postby Zorak » Mon Apr 04, 2011 9:46 am

OK, thanks for pointing this out. The export was handling the field type properly and handling the data properly, but the "auto" designation in Access was coming in by default and getting rid of everything after the decimal. We just needed to define the "scale" in the create table's field definition and now it's working fine. We will be distributing this fix in the next Maintenance Release.
Zorak
Tech Support
Tech Support
 
Posts: 2493
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Exporting to MDB

Postby danielhugh@gmail.com » Sat Oct 13, 2018 10:43 am

Zorak wrote:OK, thanks for pointing this out. The export was handling the field type properly and handling the data properly, but the "auto" designation in Access was coming in by default and getting rid of everything after the decimal. We just needed to define the "scale" in the create table's field definition and now it's working fine. We will be distributing this fix in the next Maintenance Release.


PowerChurchPlus 11.5 using Windows 7
Is it possible that a similar issue still exists during exports to MDB with date formatting? I am experiencing a problem where the MM and DD for attendance dates in the ATDATA table are being flipped. It only affects the dates where the DD is 12 or under, this means about 1/3 of the dates are flipped while 2/3 are correct. I have verified the data is correct in the PowerChurch software. Is it possible I'm missing something in my formatting options?

Dan Clark
danielhugh@gmail.com
 
Posts: 5
Joined: Fri Oct 12, 2018 2:56 pm

Re: Exporting to MDB

Postby NeilZ » Sun Oct 14, 2018 4:54 pm

danielhugh@gmail.com wrote:
Zorak wrote:OK, thanks for pointing this out. The export was handling the field type properly and handling the data properly, but the "auto" designation in Access was coming in by default and getting rid of everything after the decimal. We just needed to define the "scale" in the create table's field definition and now it's working fine. We will be distributing this fix in the next Maintenance Release.


PowerChurchPlus 11.5 using Windows 7
Is it possible that a similar issue still exists during exports to MDB with date formatting? I am experiencing a problem where the MM and DD for attendance dates in the ATDATA table are being flipped. It only affects the dates where the DD is 12 or under, this means about 1/3 of the dates are flipped while 2/3 are correct. I have verified the data is correct in the PowerChurch software. Is it possible I'm missing something in my formatting options?

Dan Clark


FWIW .. I suspect its more about the formatting and not the export, as you're the only one who has reported anything like this.
Could you give an example of how the date looks in the Foxpro table, and what you're seeing in Access?

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 8906
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Exporting to MDB

Postby danielhugh@gmail.com » Tue Oct 16, 2018 10:45 pm

NeilZ wrote:
danielhugh@gmail.com wrote:
Zorak wrote:OK, thanks for pointing this out. The export was handling the field type properly and handling the data properly, but the "auto" designation in Access was coming in by default and getting rid of everything after the decimal. We just needed to define the "scale" in the create table's field definition and now it's working fine. We will be distributing this fix in the next Maintenance Release.


PowerChurchPlus 11.5 using Windows 7
Is it possible that a similar issue still exists during exports to MDB with date formatting? I am experiencing a problem where the MM and DD for attendance dates in the ATDATA table are being flipped. It only affects the dates where the DD is 12 or under, this means about 1/3 of the dates are flipped while 2/3 are correct. I have verified the data is correct in the PowerChurch software. Is it possible I'm missing something in my formatting options?

Dan Clark


FWIW .. I suspect its more about the formatting and not the export, as you're the only one who has reported anything like this.
Could you give an example of how the date looks in the Foxpro table, and what you're seeing in Access?


Thank you for your quick reply. I am not at all familiar with Foxpro, so I will need some pointers to inspect the tables themselves. Our Power Church application is located on a network at our church. Dates in our Power Church Plus application are formatted as mm/dd/yyyy. The PC we are working with at our church is also formatted the same way. When I run Activities reports from Power Church the data appears on the Sundays it was entered. After running the Export to Access MDB tables I noted Sundays with missing data. Eventually I was able to locate that data by reversing the position of mm and dd. Of course the misplaced data no longer appears on Sundays. Some of it is appearing in the remaining days of October, November, and December of 2018.

I tried to run reports in Power Church that covered dates in the future but was unable to. I thank you for any direction you can provide me with.

Dan Clark
danielhugh@gmail.com
 
Posts: 5
Joined: Fri Oct 12, 2018 2:56 pm

Re: Exporting to MDB

Postby NeilZ » Wed Oct 17, 2018 7:21 am

danielhugh@gmail.com wrote:Thank you for your quick reply. I am not at all familiar with Foxpro, so I will need some pointers to inspect the tables themselves. Our Power Church application is located on a network at our church. Dates in our Power Church Plus application are formatted as mm/dd/yyyy. The PC we are working with at our church is also formatted the same way. When I run Activities reports from Power Church the data appears on the Sundays it was entered. After running the Export to Access MDB tables I noted Sundays with missing data. Eventually I was able to locate that data by reversing the position of mm and dd. Of course the misplaced data no longer appears on Sundays. Some of it is appearing in the remaining days of October, November, and December of 2018.

I tried to run reports in Power Church that covered dates in the future but was unable to. I thank you for any direction you can provide me with.

Dan Clark


Foxpro stores the date as YYYYMMDD, but the export sends it as a Access 2002-2003 database Short DATE format which is displayed as M/D/YYYY. For example, if the date is 10/01/2018 on Foxpro, in Access you will see 10/1/2018.

Are you taking that into account?

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 8906
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Exporting to MDB

Postby danielhugh@gmail.com » Wed Oct 17, 2018 3:35 pm

NeilZ wrote:
danielhugh@gmail.com wrote:Thank you for your quick reply. I am not at all familiar with Foxpro, so I will need some pointers to inspect the tables themselves. Our Power Church application is located on a network at our church. Dates in our Power Church Plus application are formatted as mm/dd/yyyy. The PC we are working with at our church is also formatted the same way. When I run Activities reports from Power Church the data appears on the Sundays it was entered. After running the Export to Access MDB tables I noted Sundays with missing data. Eventually I was able to locate that data by reversing the position of mm and dd. Of course the misplaced data no longer appears on Sundays. Some of it is appearing in the remaining days of October, November, and December of 2018.

I tried to run reports in Power Church that covered dates in the future but was unable to. I thank you for any direction you can provide me with.

Dan Clark


Foxpro stores the date as YYYYMMDD, but the export sends it as a Access 2002-2003 database Short DATE format which is displayed as M/D/YYYY. For example, if the date is 10/01/2018 on Foxpro, in Access you will see 10/1/2018.

Are you taking that into account?


Yes, I believe I am. The part I find confusing is that this year we've had just over 40 Sundays so far. Of those Sundays 16 don't show any Activity data entries for the ATDATA table in Access. I have now found each one of those missing entries by reversing the month and the day while searching in the ATDATA table. This includes three dates of data that are still in the future, 11/2/2018, 11/3/2018, and 12/8/2018. Data for the remaining 27 Sundays is displayed on the dates expected. Why would 1/3 of the entries end up reversed while the other 2/3 of the entries are just fine I wonder?

When I go back to Power Church I've checked and found that data for every one of the Sundays from Jan 7 to the present (Oct 14) is displayed on the proper date, and can be reported on via the built in Power Church reports. Thank you again for your time and care.

Dan Clark
danielhugh@gmail.com
 
Posts: 5
Joined: Fri Oct 12, 2018 2:56 pm

Re: Exporting to MDB

Postby NeilZ » Wed Oct 17, 2018 8:48 pm

danielhugh@gmail.com wrote:
Yes, I believe I am. The part I find confusing is that this year we've had just over 40 Sundays so far. Of those Sundays 16 don't show any Activity data entries for the ATDATA table in Access. I have now found each one of those missing entries by reversing the month and the day while searching in the ATDATA table. This includes three dates of data that are still in the future, 11/2/2018, 11/3/2018, and 12/8/2018. Data for the remaining 27 Sundays is displayed on the dates expected. Why would 1/3 of the entries end up reversed while the other 2/3 of the entries are just fine I wonder?

When I go back to Power Church I've checked and found that data for every one of the Sundays from Jan 7 to the present (Oct 14) is displayed on the proper date, and can be reported on via the built in Power Church reports. Thank you again for your time and care.

Dan Clark


FWIW ... 11/2/2018, could be 2/11/2018, 11/3/2018 could be 3/11/2018, and 12/8/2018 could be 8/12/2018.

Those are all Sundays, while 11/2 & 11/3 are not, and 12/8 is not

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 8906
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Exporting to MDB

Postby danielhugh@gmail.com » Thu Oct 18, 2018 11:57 am

NeilZ wrote:
danielhugh@gmail.com wrote:
Yes, I believe I am. The part I find confusing is that this year we've had just over 40 Sundays so far. Of those Sundays 16 don't show any Activity data entries for the ATDATA table in Access. I have now found each one of those missing entries by reversing the month and the day while searching in the ATDATA table. This includes three dates of data that are still in the future, 11/2/2018, 11/3/2018, and 12/8/2018. Data for the remaining 27 Sundays is displayed on the dates expected. Why would 1/3 of the entries end up reversed while the other 2/3 of the entries are just fine I wonder?

When I go back to Power Church I've checked and found that data for every one of the Sundays from Jan 7 to the present (Oct 14) is displayed on the proper date, and can be reported on via the built in Power Church reports. Thank you again for your time and care.

Dan Clark


FWIW ... 11/2/2018, could be 2/11/2018, 11/3/2018 could be 3/11/2018, and 12/8/2018 could be 8/12/2018.

Those are all Sundays, while 11/2 & 11/3 are not, and 12/8 is not


That is correct. The first one contains the data from Sunday 2/11/2018. When the 'mm' and 'dd' are interchanged this data ends up in a future date which happens to be a Friday. The second one contains the data from Sunday 3/11/2018. With 'mm' and 'dd' interchanged this data ends up in a future date which happens to be a Saturday. The third one contains the data from Sunday 8/12/2018. With 'mm' and 'dd' reversed this data ends up in a future date which happens to be a Saturday.

This happened to a total of 16 of the Sundays to date. In the ATDATA table in Access the correct Sunday date has no data, but I found appropriate data in the date that results when the 'mm' and the 'dd' are interchanged. It seems that the dates that ended up being interchanged in this manner were only the dates where interchanging is viable without modifying the data. Here is an example of the first few: 2/4/2018 interchanged as 4/2/2018; 2/11/2018 interchanged as 11/2/2018; 3/4/2018 interchanged as 4/3/2018; 3/11/2018 interchanged as 11/3/2018. This goes on for every Sunday date where the 'mm' is less than 13 (16 Sundays). There is one exception, both 1/7/2018 and 7/1/2018 appear to have their appropriate data in place.

Dan Clark
danielhugh@gmail.com
 
Posts: 5
Joined: Fri Oct 12, 2018 2:56 pm

Re: Exporting to MDB

Postby danielhugh@gmail.com » Thu Oct 18, 2018 5:49 pm

danielhugh@gmail.com wrote:
NeilZ wrote:
danielhugh@gmail.com wrote:
Yes, I believe I am. The part I find confusing is that this year we've had just over 40 Sundays so far. Of those Sundays 16 don't show any Activity data entries for the ATDATA table in Access. I have now found each one of those missing entries by reversing the month and the day while searching in the ATDATA table. This includes three dates of data that are still in the future, 11/2/2018, 11/3/2018, and 12/8/2018. Data for the remaining 27 Sundays is displayed on the dates expected. Why would 1/3 of the entries end up reversed while the other 2/3 of the entries are just fine I wonder?

When I go back to Power Church I've checked and found that data for every one of the Sundays from Jan 7 to the present (Oct 14) is displayed on the proper date, and can be reported on via the built in Power Church reports. Thank you again for your time and care.

Dan Clark


FWIW ... 11/2/2018, could be 2/11/2018, 11/3/2018 could be 3/11/2018, and 12/8/2018 could be 8/12/2018.

Those are all Sundays, while 11/2 & 11/3 are not, and 12/8 is not


That is correct. The first one contains the data from Sunday 2/11/2018. When the 'mm' and 'dd' are interchanged this data ends up in a future date which happens to be a Friday. The second one contains the data from Sunday 3/11/2018. With 'mm' and 'dd' interchanged this data ends up in a future date which happens to be a Saturday. The third one contains the data from Sunday 8/12/2018. With 'mm' and 'dd' reversed this data ends up in a future date which happens to be a Saturday.

This happened to a total of 16 of the Sundays to date. In the ATDATA table in Access the correct Sunday date has no data, but I found appropriate data in the date that results when the 'mm' and the 'dd' are interchanged. It seems that the dates that ended up being interchanged in this manner were only the dates where interchanging is viable without modifying the data. Here is an example of the first few: 2/4/2018 interchanged as 4/2/2018; 2/11/2018 interchanged as 11/2/2018; 3/4/2018 interchanged as 4/3/2018; 3/11/2018 interchanged as 11/3/2018. This goes on for every Sunday date where the 'mm' is less than 13 (16 Sundays). There is one exception, both 1/7/2018 and 7/1/2018 appear to have their appropriate data in place.

Dan Clark


A Great thing happened today! We found a formatting issue on the church computer that we were using to export the Power Data to Access Tables. Thank you very much for your time effort and responses. They kept me digging here.

Thank you again,

Dan Clark
danielhugh@gmail.com
 
Posts: 5
Joined: Fri Oct 12, 2018 2:56 pm