Hi folks,

I have the following sql statement in my code.


strSQL = "INSERT INTO tbl_iss_num (doc_tbl_fk, Issue_num, Date_Released) VALUES " & _
"(" & fk_ver & ", '" & Me!txt_ver_no & "'," & Date & ")"


When I use db.execute (strSQL) the value inserted into the table by the date function is 30/12/1899. Anyone know why this is happening?

that date is the value of date 0

display the strsql in a msgbox to see what it looks like, as somehow it is setting the date to date 0.

Using a messagebox or debug.print for the date function returns the correct date. When I use it as part of a sqlstring it returns the stated date.

I've never done this but I'm guessing that Date used like that does not work, can you set a field = Date and use that in the SQL string.

Brian

Thing is I'm sure I have done this in the past.

Even if I do

dim mydate as date

mydate = date


if I debug.print the mydate variable when the code is run todays date appears in the immediate window but the value inserted into the table is 31/12/1899.

i dont mean do msgbox on the datefunction
i mean do msgbox(strsql) to see exactly what you have got in the string

you've not got a control on the form called date have you, or a temporary variable in the form called date , or even a field in the table called date - that might be why the intrinsic date function is not getting called

--------
the only other thing is the brackets around the values string - i dont think the HAVE to be there - so i'm not sure whether actually having them there misleads access

ie

insert into mytable (field1, field2, field3) values 12,13,14

but you have

insert into mytable (field1, field2, field3) (values 12,13,14)

so im not sure if that makes a difference

When I msgbox strSQL the correct date value is being displayed in the string. When I go into the table it is still displaying the 31/12/1899 for the newly inserted record.

When I msgbox strSQL the correct date value is being displayed in the string. When I go into the table it is still displaying the 31/12/1899 for the newly inserted record.

What format is the date in? Although if it's today's date I can't see it making much difference as it will either be the fourth of March or the 3rd of April....

Ah! It's a date, you need to put # date # around it I reckon.

You are absolutley right I do need the #'s. Many thanks