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.

[aytee111]

Issue 1:

That field is setup as text in design view

What does that mean - design view of the import table? When importing from Excel the data type of the field is determined by the first few rows, so it your first few rows are numeric then the import assumes it is a number data type and you will have the above-mentioned problems when non-numeric characters are encountered.

Issue 2: if 64.1 is different to 64.10 then it needs to be a text field. It must not be stored on a table as it is a calculated field and is always changing. Create a standard query or function which you use throughout your database whenever you need to display this field.

Issue 3 - see issue 2