Today's date in Access

  • Thread starter Thread starter James
  • Start date Start date
J

James

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.

Thanks

James
 
James,

Could you post the code you're trying to use please?

I use the VBA function Now() to get the current system
date/time. I suspect your problem lies in how you're trying
to store it.

Don't forget that Access actually stores dates internally in
US format. If you're expecting dd/mm/yy when examining the
raw date you may have trouble.

Some untested suggestions;
1) You could put a hidden control bound to your log record
date field on your form with the default value set to
=Now().
2) You could set the default value for the field in the
table itself to Now().

Good luck
 
James,

Without knowing the details of what you are doing, it is hard to tell
what the problem is. By the way, 31/12/1899 isn't rubbish, it's the
date with a value of 1 so this might give a clue as to where your
mistake lies. My guess would be to use code similar to this on the
Click event of your command button...
Me.TransactionDate = Date
 
If you are talking about the current form, and that table that the form is
bound to, then it takes ONE LINE of code.


me!MyDateField = date()

I don't think oracle, or any other system on the planet is going to be less
then one line of code...since that would mean no code!

It is not clear if mean update the time to the current table, or you wish to
write out a new record to antoher table each time the current record is
modified?

That being the case, you could use the after udate event:

dim strSql as string

strSql = "INSERT INTO tblLog (fk_id, dtStamp) & _
" values (" & me.id & ", #" & formate(date,"mm/dd/yyyy") &
"# )"
currentdb.Execute strSql

The aboove would udpate to a another table, and you could place the above in
the after update event of the form. I could 3 lines max of code, and again,
I don't see oracle, or any other platform being less lines of code.
 
James said:
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.
Click to expand...

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.
 
Albert,

Not wishing to contradict, but...
I don't think oracle, or any other system on the planet is going to be less
then one line of code...since that would mean no code!
Click to expand...

.... for a no-code approach, see Access Help for SetValue macro action <g>
 
Thanks Abert!
My problem was syntax, which your SQL string fixed.

Appreciate your help

James Mc
 
Back
Top