Inserting Date values in Access 2000

I have an Access 2000 database with a table that has a number of Date/Time fields in it. Each field is set to format Short Date.

When in Visual Basic 6 I try and Insert a row into the table, I use the Execute method of an ADO connection object to run an in-line SQL statement. The Dates which are to be entered are all in correct Short Date format. The row is entered but all the Date/Time fields all show their value as 30/12/1899.

Why is this?
How come the correct date from the SQL statement aren't saved?
HelpAndyAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Use the following format for your dates: yyyy-mm-dd

So to build on gencross's solution:
Const YMDMask = "yyyy-mm-yy"
myConn.Execute "INSERT INTO Holidays (DateFrom, DateTo) VALUES (#" & Format$(dtDateFrom, YMDMask) & "#,#" & Format$(dtDateTo, YMDMask) & 
"#);"
0
 
Anthony PerkinsCommented:
The way the data is formatted in Access is totally unrelated to how it is stored.  Try saving with mdy format, also post your code and we can be in a better position to help you.

Anthony
0
 
HelpAndyAuthor Commented:
Up till now I have used:
myConn.Execute "INSERT INTO Holidays (DateFrom, DateTo) VALUES (" & dtDateFrom & "," & dtDateTo & ");"

dtDateForm and dtDateTo are local varaibles of the Date type.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
gencrossCommented:
I have not worked much with Acc2000 but How about this...

myConn.Execute "INSERT INTO Holidays (DateFrom, DateTo) VALUES (#" & dtDateFrom & "#,#" & dtDateTo & "#);"
0
 
HelpAndyAuthor Commented:
It works and the correct dates are put in but one of the dates is getting the day and month the wrong way around.
It seems to only happen if the date of day is less than 12.
0
 
HelpAndyAuthor Commented:
I like it perkins hold on a sec...
0
 
HelpAndyAuthor Commented:
Thank you very much and others for your input.
It was creating and formating to the Constant that ensured the date went in correctly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.