Date Function Returns 30/12/1899 (1 Viewer)

chergh · Mar 3, 2008

Hi folks,

I have the following sql statement in my code.

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?

gemma-the-husky · Mar 3, 2008

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.

chergh · Mar 3, 2008

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.

Brianwarnock · Mar 3, 2008

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

chergh · Mar 3, 2008

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

Even if I do

Code:

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.

gemma-the-husky · Mar 3, 2008

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

chergh · Mar 3, 2008

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.

Uncle Gizmo · Mar 4, 2008

chergh said:
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.

chergh · Mar 4, 2008

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

Date Function Returns 30/12/1899 (1 Viewer)

chergh

blah

gemma-the-husky

Super Moderator

chergh

blah

Brianwarnock

Retired

chergh

blah

gemma-the-husky

Super Moderator

chergh

blah

Uncle Gizmo

Nifty Access Guy

chergh

blah

Similar threads

Users who are viewing this thread