Access 2007 Import Issues-Calculating Age Correctly

[Escondido]

Issue 1- I have Address and Address2 setup in my database as fields. Address2 is specifically used for apartment numbers and the like. In one of my
my spreadsheets there are about 33K cells listed in the Address2 column and Access was able to import all of them except for about 5179 cells. I've briefly
looked at the "type conversion" errors per the spreadsheet and cannot seem to find out what is causing the issue. Some examples of what didn't get
import correctly are as follows.

C3
B
B141
T
3K

I suspect for some reason that Access is having a problem importing the fields with a letter for some reason. That field is setup as text in
design view. Does anybody know what might be causing this issue and how to fix it?

Issue 2- I set the Age column to currency, decimal places to 2, but it's still not correct. For example, somebody born on 12/1/1952
would be 64.10 (number of years.number of months) and Access correctly shows that number, but somebody born 9/1/1952 is incorrectly shown as 64.10
when it should be 64.1

I've tried different variations (1 decimal, 2 decimal, using text format, etc.) using Design View and nothing seems to work as smoothly as Excel when choosing to format numbers correctly. Does
anybody have a solution or workaround?

This presents a major problem too due to the fact that Access doesn't seem to like it when I setup a Query to find anybody with a zero
in their age. A thought I had, which I'd like to avoid if possible, is to setup 2 additional columns called Age (Years) Age (Months), but then
I gotta figure out how to calculate that correctly too when the aforementioned option in the 1st paragraph would be ideal. I'd love to hear
if somebody else has encountered this issue and what they did to fix it.

Issue 3- Is it possible to have Access automatically calculate the age when I run a macro, open the file, etc. and display the age in
in xx.xx format (e.g. 57.10 for 57 years, 10 months). All the code and options I have found online seem to point to the fact that Access will
only calculate and display the information in X year, Y months format. Anybody know of a workaround?

Thanks.

 

[The_Doc_Man]

Your first major problem for #2 and #3 is that since we have 12 months in a year (thus making calendar fractions duodecimal) but currency is inherently decimal-based for its fractions, you are looking at a non-standard and non-traditional fraction representation. Further, showing "1" for someone with a one-month remainder on their years and showing "10" for someone with a ten-month remainder is actually a TEXT-formatted representation. To be consistent, you would have needed to show the one-month case as "01" to make it fit. Don't expect Access to do this for you. It won't happen. You would have to "roll your own" formatting function to do this.

There is also the problem that if you tried to do remaindering, the lengths of the months are not uniform, so your "apportioning" of months of age will depend on the month of birth and the current month. Just for snorts and giggles, take ME for example. I was born in February of a LEAP YEAR, so my age will be tricky to compute in years and months by any algorithm you want for years that aren't leap years. (Not that others born in non-leap years are necessarily easier.)

For #3, you can certainly include a "RunCode" action in a macro and have that code do some sort of computation. If there is a handy form, the RunCode surely could deposit the result of that computation somewhere. But regarding that chosen format, see my comments regarding #2.

For #1, this is a common problem. To do an import from Excel to Access, you need code that can translate Excel cells (which are internally faceless i.e. probably stored as a string with a format code for numeric conversions) and code that can store Access data formats (which are NEVER faceless i.e. if the field type is LONG then a 32-bit integer IS stored).

These conversions are usually parts of the libraries that are loaded as "baggage" when you install Office. (Don't take "baggage" with negative connotation - it is baggage that you NEED.) It is not clear to me as to whether Excel's code or Access's code is actually running at the point that the cell's content gets converted. The problem occurs when these two disparate parts don't communicate perfectly as to what they need to do.

The only solutions that I have seen here for your specific case include either (a) writing VBA code to perform an Excel Application Object operation to scan the cells and an Access Recordset operation to store the data, or (b) MAP the spreadsheet (temporarily, at least, via Linked Table Manager, which you can do easily for spreadsheets that you are not updating from Access) and use a query with explicit type conversions for the INSERT INTO statement so that the given source fields have no choice but to be interpreted as text.

However, in a few rare cases, there have been some sneaky tricks...

For instance, some of our members believe (and I don't doubt) that it is the first several rows of the spreadsheet that "fixate" the conversion process for those rows. In your case, the number of text entries for Address2 in your spreadsheets is low enough that it is not hard to believe that the first several rows would give the conversion code the wrong idea about the data.

As a quick and dirty attempt, can you pre-sort the Excel sheet? Sort it such that you present the data set sorted by Address2 (Descending) - which would force the alphabetic apartment designations to appear earlier.

Remember that you can use ORDER BY clauses in queries to sort the output when feeding forms or reports. So the order in which the records are imported is almost meaningless anyway - or if it is not, it SHOULD be. If this pre-sort "tricks" the import into using the correct data type for your import, then you have a work-around solution.

 

[Escondido]

Your first major problem for #2 and #3 is that since we have 12 months in a year (thus making calendar fractions duodecimal) but currency is inherently decimal-based for its fractions, you are looking at a non-standard and non-traditional fraction representation. Further, showing "1" for someone with a one-month remainder on their years and showing "10" for someone with a ten-month remainder is actually a TEXT-formatted representation. To be consistent, you would have needed to show the one-month case as "01" to make it fit. Don't expect Access to do this for you. It won't happen. You would have to "roll your own" formatting function to do this.

There is also the problem that if you tried to do remaindering, the lengths of the months are not uniform, so your "apportioning" of months of age will depend on the month of birth and the current month. Just for snorts and giggles, take ME for example. I was born in February of a LEAP YEAR, so my age will be tricky to compute in years and months by any algorithm you want for years that aren't leap years. (Not that others born in non-leap years are necessarily easier.)

For #3, you can certainly include a "RunCode" action in a macro and have that code do some sort of computation. If there is a handy form, the RunCode surely could deposit the result of that computation somewhere. But regarding that chosen format, see my comments regarding #2.

For #1, this is a common problem. To do an import from Excel to Access, you need code that can translate Excel cells (which are internally faceless i.e. probably stored as a string with a format code for numeric conversions) and code that can store Access data formats (which are NEVER faceless i.e. if the field type is LONG then a 32-bit integer IS stored).

These conversions are usually parts of the libraries that are loaded as "baggage" when you install Office. (Don't take "baggage" with negative connotation - it is baggage that you NEED.) It is not clear to me as to whether Excel's code or Access's code is actually running at the point that the cell's content gets converted. The problem occurs when these two disparate parts don't communicate perfectly as to what they need to do.

The only solutions that I have seen here for your specific case include either (a) writing VBA code to perform an Excel Application Object operation to scan the cells and an Access Recordset operation to store the data, or (b) MAP the spreadsheet (temporarily, at least, via Linked Table Manager, which you can do easily for spreadsheets that you are not updating from Access) and use a query with explicit type conversions for the INSERT INTO statement so that the given source fields have no choice but to be interpreted as text.

However, in a few rare cases, there have been some sneaky tricks...

For instance, some of our members believe (and I don't doubt) that it is the first several rows of the spreadsheet that "fixate" the conversion process for those rows. In your case, the number of text entries for Address2 in your spreadsheets is low enough that it is not hard to believe that the first several rows would give the conversion code the wrong idea about the data.

As a quick and dirty attempt, can you pre-sort the Excel sheet? Sort it such that you present the data set sorted by Address2 (Descending) - which would force the alphabetic apartment designations to appear earlier.

Remember that you can use ORDER BY clauses in queries to sort the output when feeding forms or reports. So the order in which the records are imported is almost meaningless anyway - or if it is not, it SHOULD be. If this pre-sort "tricks" the import into using the correct data type for your import, then you have a work-around solution.

Your response really helped me structure my data. I appreciate the thorough response.

 

[The_Doc_Man]

No problem. If that resolved the issue, please mark the thread as SOLVED. Others might then benefit from it.