Re: Today's date in Access

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 06/20/04 

Date: Sun, 20 Jun 2004 17:34:20 -0400

"James" <anonymous@discussions.microsoft.com> wrote in message
news:1e9b301c45695$a3bd3b20$a301280a@phx.gbl
> How can I use a form button to insert today's date in a
> table, to leave a transction log?
>
> I have tried putting the current date in a SQL string
> that I run with doCmd in VBA, but if the data type in the
> table is date/time, I get rubbish (31/12/1899, or some
> such). It looks OK if I change the data type in the
> table to string, but I need it to be a date type for
> calculation.
>
> This would be dead easy in Oracle.

It's dead easy in Access, too, but clearly you're doing something wrong.
You'll need to post your code to let us see what. My guess is that
you've failed to properly delimit the date value with hash-marks (#),
the date delimiter. You may have written something like this:

    '*** WRONG ***
    DoCmd.RunSQL _
        "INSERT INTO MyTable(MyField) " &
        "VALUES(" & Date & ");"

Assuming your regional date format is dd/mm/yyyy, that would result in
the execution of this SQL statement:

    INSERT INTO MyTable(MyField) VALUES(20/6/2004);

which is the same as saying

    INSERT INTO MyTable(MyField) VALUES(1.66333998669328E-03);

which is *not* what you had in mind!

Instead, you could write this (letting the Jet expression service get
the current date value):

    '*** RIGHT #1 ***
    DoCmd.RunSQL _
        "INSERT INTO MyTable(MyField) VALUES(Date());"

or else clearly specify the value as a date literal (in US format), like
this:

    '*** RIGHT #2 ***
    DoCmd.RunSQL _
        "INSERT INTO MyTable(MyField) " &
        "VALUES(#" & Format(Date, "mm/dd/yyyy") & "#);"

That would give you this SQL string:

    INSERT INTO MyTable(MyField) VALUES(#6/202004#);

which would be interpreted correctly. 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Quantcast