Numeric Field Overflow when appending date - how to convert it to text

Joined
Jul 30, 2012
Messages
4
Reaction score
0
Hell all,
I sure hope someone can help because I'm frustrated beyond belief. I've searched for hours for a solution to my specific problem and cannot find one.

I'm working in MS Access 2003. I have an Excel file that I link to that contains data downloaded from a customer's portal. I am appending this data into a table in Access.

By process of elimination I have determined that the Numeric Field Overflow error is occurring on one field, which is a date field. (I tried appending just the suspect column to a table that has one field.)

Usually the data is a Monday date in "07/30/12" format but sometimes the customer has "0000/00/00" in that field. The first time I run the append query after a change I get I do not get the error but I do after subsequent runs. If I remove those rows from the file I do not get the overflow error. However, this is not a workable solution because I need to have those rows in the table but it did help me determine the exact cause of the problem. I simply cannot figure out how to get around this error.

So, my next thought was to convert that column of data to text but again, I am stumped. I couldn't figure out what function to use to convert the date to text/string. I tried changing the entire date column to Text format in the Excel file but the dates got converted to numbers.

Has anyone found a workaround for this specific problem? Any help would be appreciated!

Frustrated Jan :cry:
 
Back
Top