Set Date via VBA

[Rose B]

I have a SQL INSERT statement that should insert a record into a table using
valkues from a form. All values are going in OK apart from a date field,
which is always being set to 30/12/1899 whether I set using the form field I
really want or by setting a literal e.g. "11/11/2007". Can anyone please help
me?

 

[MAC]

I have a SQL INSERT statement that should insert a record into a table using
valkues from a form. All values are going in OK apart from a date field,
which is always being set to 30/12/1899 whether I set using the form field I
really want or by setting a literal e.g. "11/11/2007". Can anyone please help
me?

If you want to enter a default value or assign the date value in code
you will need to use the
# sign arround your date. As in #11/09/2007#. Using quotes means it
is a string.

MAC

 

[Douglas J. Steele]

You need to delimit dates with # characters (and they need to be in a format
that Jet will recognize, which generally means mm/dd/yyyy or yyyy-mm-dd.
It's NOT dependent on the user's Regional Settings.).

I'm betting that your SQL string just has 11/11/2007 for the date, rather
than #11/11/2007#. Without the delimiters, Jet actually treats it as
division, resulting in a very small number ( .0004982561). Given that Access
stores date/time values as eight-byte floating point numbers, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day, that number is 00:00:43 on the morning of 30 Dec, 1899.

 

[George Nicholson]

Without seeing your SQL, this is just a guess of the most likely issue.

SQL statements need date literals to be surrounded by #. Examples:

"INSERT yada yada...#11/11/2007#..."
"INSERT yada yada...#" & Me.ControlWithDateValue & "#..."

 

[Rose B]

Ideally I want to set it to a form value = e.g. Me.DateField, what would I
need to do for this?

 

[Rose B]

Nearly perfect!! I just now have the date (that starts out in dd/mm/yyyy)
ending in mm/dd/yyyy in the table.

 

[Rose B]

All working fine.....thanks!!!!!!

Without seeing your SQL, this is just a guess of the most likely issue.

SQL statements need date literals to be surrounded by #. Examples:

"INSERT yada yada...#11/11/2007#..."
"INSERT yada yada...#" & Me.ControlWithDateValue & "#..."