469,297 Members | 2,144 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,297 developers. It's quick & easy.

Problem with date format on update of a table column

167 100+
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!!!
Nov 23 '10 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. month = left(<date>,2)
  2. day = mid(<date>,4,2)
  3. year = right(<date>,2)
  4.  
  5. theDate = month & "/" & day & "/20" & year
  6. insertDate = format(theDate, "mm/dd/yyyy"

8 3147
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Nov 23 '10 #2
ncsthbell
167 100+
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".
Nov 23 '10 #3
ncsthbell
167 100+
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.
Nov 23 '10 #4
dsatino
393 256MB
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.
Nov 23 '10 #5
ncsthbell
167 100+
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???
Nov 24 '10 #6
ncsthbell
167 100+
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!!!
Nov 24 '10 #7
ncsthbell
167 100+
I have still not been able to resolve this issue. If anyone has any other suggestions, I would surely appreciate them!!!
Dec 9 '10 #8
colintis
255 100+
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:
Expand|Select|Wrap|Line Numbers
  1. month = left(<date>,2)
  2. day = mid(<date>,4,2)
  3. year = right(<date>,2)
  4.  
  5. theDate = month & "/" & day & "/20" & year
  6. insertDate = format(theDate, "mm/dd/yyyy"
Dec 10 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

15 posts views Thread by Simon Brooke | last post: by
9 posts views Thread by insomniux | last post: by
2 posts views Thread by Hennie | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.