Exporting to MDB

Moderators: Moderators, Tech Support

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

Exporting to MDB

Post by NorwalkUMC »

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

NeilZ
Posts: 9155
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA
Contact:

Re: Exporting to MDB

Post by NeilZ »

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.

Zorak
Tech Support
Tech Support
Posts: 2593
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software
Contact:

Re: Exporting to MDB

Post by Zorak »

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.

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

Re: Exporting to MDB

Post by danielhugh@gmail.com »

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

NeilZ
Posts: 9155
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA
Contact:

Re: Exporting to MDB

Post by NeilZ »

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.

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

Re: Exporting to MDB

Post by danielhugh@gmail.com »

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

NeilZ
Posts: 9155
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA
Contact:

Re: Exporting to MDB

Post by NeilZ »

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.

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

Re: Exporting to MDB

Post by danielhugh@gmail.com »

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

NeilZ
Posts: 9155
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA
Contact:

Re: Exporting to MDB

Post by NeilZ »

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.

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

Re: Exporting to MDB

Post by danielhugh@gmail.com »

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

Post by danielhugh@gmail.com »

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

Locked