Insert Current Date into MS Access 2000

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

How do I insert the current date into MS Access?

Currently I am attempting:

s.executeUpdate("INSERT INTO Purchase(timestamp,
importer_id, supplier_id, ff_id, status) VALUES(#Now()# , "
+ getUserId() + ", " + s_id + ", 1, 0)");

However, this always results in a syntax error. Attempting
to enter a hard-coded date (12/7/02) also ends in a similar
error.

However, I am more interested in knowing how to insert the
current date.
 
Andrew said:
How do I insert the current date into MS Access?

Currently I am attempting:

s.executeUpdate("INSERT INTO Purchase(timestamp,
importer_id, supplier_id, ff_id, status) VALUES(#Now()# , "
+ getUserId() + ", " + s_id + ", 1, 0)");

However, this always results in a syntax error. Attempting
to enter a hard-coded date (12/7/02) also ends in a similar
error.

However, I am more interested in knowing how to insert the
current date.
Click to expand...
Add a space after "VALUES" and remove the delimiters around the call to Now()
and you should be good to go.
 
If importer_id is a text field, you need quotes around the results of
getUserId()

Try:

s.executeUpdate("INSERT INTO Purchase (timestamp," & _
importer_id, supplier_id, ff_id, status) VALUES (" & _
Format(Date(), "\#mm\/dd\/yyyy\#") & _
"'" & getUserId() & "', " & s_id & ", 1, 0)");

That funny formatting I'm using in conjunction with the Date function
ensures that the date is in the proper mm/dd/yyyy format in the event that
the user has set his/her short date format to something else.
 
Thanks to both of you for your help - as it happens, the
'#' signs were not needed around the Now() command, and
strangely enough, access seems to accept the Aussie
dd/mm/yy style of date-ing (at least, it has accepted
18/01/04 without any problems!)

More interestingly, there was another problem - "timestamp"
is apparantly a reserved Access keyword (I checked it up on
a whim) - so the solution I am using is:

s.executeUpdate("INSERT INTO Purchase([timestamp],
importer_id, supplier_id, ff_id, status) " +
"VALUES (Now(), " + getUserId() + ", " + s_id + ", 1, 0)"
);

Which probably formats horribly here.

Thanks again!
Andrew
 
Back
Top