Formatting Date

Lol Owen

Registered User.
Local time
Today, 22:01
Joined
Mar 15, 2004
Messages
86
Hi all, got a puzzler. The code below inserts records into a connected Access table. problem is, when I look at the table, the date is 1899!

SQL = "Insert Into tblTransaction (TransactionNumber,UserID,SellDate) Values (" & intTransaction & "," & intUser & "," & Format(Now, "Short Date") & ");"

Anyone got any ideas as to where I'm going wrong? In the Access table the field is set to Date() by default. i could leave this but how do I go about the Insert statement? Surely if I specifiy the field to be inserted then I have to provide a value for it?

Thoughts please :confused:

Cheers, Lol :D
 
If you have default value in the table, just drop the field from the query.

SQL = "Insert Into tblTransaction (TransactionNumber,UserID) Values (" & intTransaction & "," & intUser & ");"

When dealing with dates, remember Access likes them delimited with hash (#), and be careful to rely upon the "short date" format, see for instance

http://allenbrowne.com/ser-36.html
http://www.mvps.org/access/datetime/date0005.htm

.. #" & format$(now,"yyyy-mm-dd hh:nn:ss") & "#)"
.. #" & format$(date,"yyyy-mm-dd") & "#)"
 
Thanks Roy! I was getting mixed up with an SQL dbase and an Access one. Seeing as I'm just learning SQL, and the VB I'm doing is new territory as well, it ain't too bad ;)

Thanks for your help, Lol :D
 

Users who are viewing this thread

Back
Top Bottom