Date problem in access
I mis coded a sql statement in a form that I didn't catch until a month afterward. Instead of "#Date()# I used "'Date()'" to insert the current date. Most of those dates are 6/22/1894 and then count downward. I was going to loop thru the table and try to convert them to the correct date that it should have been but I have no clue where to start. Thanks for the help!
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
How will you know the correct date?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I honestly was hoping someone had done this in the past. It took the date() function and got some data from it. It then indserted it into the table and took what ever it got and thought it was 120 years ago. I was hoping someone could tell me how to convert that 6/21/1894 into the original data and then I could reinsert it but be the correct date. Does that make any sense? I think they are consistent but not sure.
ASKER
I'll add I fixed the problem so it is not doing it anymore but I have 2000 rows of old data I need to fix.
you can run an update query like this
update tablex
set [datefield]=dateadd("yyyy"
where year([datefield]) < 1900
create a backup copy of the table before running the update query
update tablex
set [datefield]=dateadd("yyyy"
where year([datefield]) < 1900
create a backup copy of the table before running the update query
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My original was with single quotes.
So it was something like strSQL = UPDATE table 1 SET dtmdate = '" & Date() & "' Where ID = ID;"
That is not actually it but with some short hand.
I might have used Date$() to make it a string
Date() comes back 2/23/2011
Date$() comes back 2-23-2011
So it was something like strSQL = UPDATE table 1 SET dtmdate = '" & Date() & "' Where ID = ID;"
That is not actually it but with some short hand.
I might have used Date$() to make it a string
Date() comes back 2/23/2011
Date$() comes back 2-23-2011
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.