Solved

Inserting Date values in Access 2000

Posted on 2002-05-31
7
169 Views
Last Modified: 2010-05-02
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?
0
Comment
Question by:HelpAndy
  • 4
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 

Author Comment

by:HelpAndy
Comment Utility
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
 
LVL 4

Expert Comment

by:gencross
Comment Utility
I have not worked much with Acc2000 but How about this...

myConn.Execute "INSERT INTO Holidays (DateFrom, DateTo) VALUES (#" & dtDateFrom & "#,#" & dtDateTo & "#);"
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:HelpAndy
Comment Utility
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
Comment Utility
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
 

Author Comment

by:HelpAndy
Comment Utility
I like it perkins hold on a sec...
0
 

Author Comment

by:HelpAndy
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now