Problem with date format on update of a table column

[ncsthbell]

Access 2007 - I have a temporary table with a column named 'verified_dt' with datatype as date/time. When I look in the table this field is formatted correctly as "10/28/10". This data in this table is exported in from an excel spreadsheet.

I use this as a work table and then do updates on the 'real' table. I am updating a production table with a column(same name, same format), however, when I look at the results after the update statement runs, it does not show the same format for the date. I have used various versions of the 'format' statement as an attempt to get the value "10/28/10" with no luck. My latest attempt was format(tmptable.verified_dt, "mm/dd/yy") the result I am seeing in prodTable.verified_dt is "1:12:00AM".

Any help would be appreciated!!!

[TheSmileyCoder]

I would start by looking at whether the ProdTable.Verified_dt has a format set.

Its the format of the table that determines how it is presented. Then of course you can override that setting when making a query/form/report. But if your looking at the dates directly in the table, it is the format of the table that determines how it is presented.

[ncsthbell]

I looked at the column in the table and it just had the datatype set as date/time, the format was blank. I chose 'short date' as the format. Now my result is "12/30/1899" instead of "1:12:00AM".

[ncsthbell]

I changed the format to 'mm/dd/yyyy' and still get
"12/30/1899". I noticed when I click in the column it will display the time.

[dsatino]

When you import from excel, Access must make a decision on what the datatype each field is. In your case, it looks like access is seeing your excel date as a number.

So when it imports your data, it sees the slashes as division symbols and it does the math. The result of 10/28/10 = .035714 which translates to 12/30/1899 12:51 AM.

Obviously your getting a 1:12 AM which doesn't really make sense unless you consider how Access may handle the numeric value behind dates. From your results, it looks like Access rounds to the nearest factor of 5.

In any case, your problem lies in how Access percieves your data. When importing from Excel, it doesn't consider each cell. It makes it's decision based on the first data cell in the column. Get that right, and you'll be ok.

[ncsthbell]

Every value in the cell in the excel spreadsheet is a date so access should percieve my data as 'date' at least. I would not think it would interpret it as numeric when there are slashes in the date. I don't know what other way to handle figuring out what access thinks the data is???

[ncsthbell]

I have tried the following:
I have changed the datatype on the table for the date column being imported from excel column to be 'text' instead of 'date/time. My thinking was I could bring convert it from text to a date, then update the prod table with a real date value.
I tried DateSerial & CDate and STILL get the result of
'12/30/1899' when I run the code to update the column in the prod table.

I am VERY frustrated with this as I have spent many hours today on this and I have got to get it resolved to meet deadlines for my user acceptance testing. This is the only 'glich' I have in my system. I don't know how access is interpreting this column on the excel spreadsheet when it is imported. The excel SS has it with a format of 'date'. I imported it into a column on the access table of 'date/time' datatype. That didn't work so I change it to import into a 'text' datatype and that did not work.

This is probably one of those less difficult things to do and maybe I am over complicating it.

Any other suggestions would be useful.... I am worried I will not meet my deadline of EOD tomorrow (11/24/10).
THANKS!!!

[ncsthbell]

I have still not been able to resolve this issue. If anyone has any other suggestions, I would surely appreciate them!!!

[colintis]

Have you tried to split that date individually as day, month and year; then join them back together in mm/dd/yyyy format before you store them? so should be something like:

month = left(<date>,2)
day = mid(<date>,4,2)
year = right(<date>,2)
 
theDate = month & "/" & day & "/20" & year
insertDate = format(theDate, "mm/dd/yyyy"