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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
gencrossCommented:
I have not worked much with Acc2000 but How about this...

myConn.Execute "INSERT INTO Holidays (DateFrom, DateTo) VALUES (#" & dtDateFrom & "#,#" & dtDateTo & "#);"
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.