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
Exporting to MDB
Moderators: Moderators, Tech Support
Re: Exporting to MDB
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 ??
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.
Using PC+ since 1999.
-
- Tech Support
- Posts: 3014
- Joined: Thu May 13, 2004 9:59 am
- Location: PowerChurch Software
- Contact:
Re: Exporting to MDB
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.
-
- Posts: 5
- Joined: Fri Oct 12, 2018 3:56 pm
Re: Exporting to MDB
PowerChurchPlus 11.5 using Windows 7Zorak 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.
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
Re: Exporting to MDB
FWIW .. I suspect its more about the formatting and not the export, as you're the only one who has reported anything like this.danielhugh@gmail.com wrote:PowerChurchPlus 11.5 using Windows 7Zorak 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.
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
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.
Using PC+ since 1999.
-
- Posts: 5
- Joined: Fri Oct 12, 2018 3:56 pm
Re: Exporting to MDB
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.NeilZ wrote:FWIW .. I suspect its more about the formatting and not the export, as you're the only one who has reported anything like this.danielhugh@gmail.com wrote:PowerChurchPlus 11.5 using Windows 7Zorak 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.
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
Could you give an example of how the date looks in the Foxpro table, and what you're seeing in Access?
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
Re: Exporting to MDB
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.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
Are you taking that into account?
Neil Zampella
Using PC+ since 1999.
Using PC+ since 1999.
-
- Posts: 5
- Joined: Fri Oct 12, 2018 3:56 pm
Re: Exporting to MDB
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?NeilZ wrote: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.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
Are you taking that into account?
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
Re: Exporting to MDB
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.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
Those are all Sundays, while 11/2 & 11/3 are not, and 12/8 is not
Neil Zampella
Using PC+ since 1999.
Using PC+ since 1999.
-
- Posts: 5
- Joined: Fri Oct 12, 2018 3:56 pm
Re: Exporting to MDB
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.NeilZ wrote: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.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
Those are all Sundays, while 11/2 & 11/3 are not, and 12/8 is not
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
-
- Posts: 5
- Joined: Fri Oct 12, 2018 3:56 pm
Re: Exporting to MDB
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.danielhugh@gmail.com wrote: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.NeilZ wrote: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.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
Those are all Sundays, while 11/2 & 11/3 are not, and 12/8 is not
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
Thank you again,
Dan Clark