strange error in INSERT INTO VBA statement

[swarv]

I have the following:

    SQLText = "INSERT INTO absent ([start_date], [end_date], [days], [reason], [name], [fullname], [day], [absentcode], [notes], [comments], [backtowork], [certification]) SELECT #" & totalstartdate & "#, #" & totalenddate & "#, " & TotalBusinessDays & ", '" & namevalue2 & "', '" & namevalue & "', '" & fullname2 & "', '" & inputday & "', '" & recordit & "', '" & notesbox & "', '" & commentsbox & "', '" & backtoyes & "', '" & certtoyes & "'"
    DoCmd.RunSQL SQLText

It all works apart from the totalstartdate bit.
when it inserts into the database the totalstartdate shows as (example) 12/05/2009 and the totalenddate show as (example) 20/05/2009.
then when you look in the database it shows as 05/12/2009 and 20/05/2009.
Is there a reason why access is swapping the date format around for totalstartdate sometimes? and not the totalenddate? Is there a way to solve it?

This is driving me nuts.
Thanks

Swarv

 

[pbaldy]

See if this helps:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

 

[swarv]

I tried that - still no luck.

It doesn't do it to all dates - just some.

Any ideas?

 

[pbaldy]

It appears to be converting to US dates when the specific date allows it.

 

[swarv]

yes - that seems about right - is there any way of getting round that?

 

[pbaldy]

Did you read the link? The dates need to be in US format when inserted.

 

[swarv]

I have tried the following:

Function fSQLDate(varDate)
If Not IsNull(varDate) Then
fSQLDate = Format(CStr(varDate), "\#yyyy\-mm\-dd\#")
End If
End Function

    SQLText = "INSERT INTO absent ([start_date], [end_date], [days], [reason], [name], [fullname], [day], [absentcode], [notes], [comments], [backtowork], [certification]) SELECT " & fSQLDate(str1) & ", " & fSQLDate(totalenddate) & ", " & TotalBusinessDays & ", '" & namevalue2 & "', '" & namevalue & "', '" & fullname2 & "', '" & inputday & "', '" & recordit & "', '" & notesbox & "', '" & commentsbox & "', '" & backtoyes & "', '" & certtoyes & "'"
    DoCmd.RunSQL SQLText

Which seems to run fine and inserts date into the table correctly.

But when I do a report to view the data this is what I use:

strcondition = "[Start_Date] BETWEEN " & fSQLDate2(Me.txt_date) & " AND " & fSQLDate1(Me.txtEndDate) & " AND absent.absentcode = '" & countme & "' AND tbl_users.team = '" & Team & "'"
DoCmd.OpenReport "absent7", acViewPreview, , strcondition

and the 2 functions it calls:

Function fSQLDate2(varDate)
If Not IsNull(varDate) Then
fSQLDate2 = Format(CStr(varDate), "\#yyyy\-mm\-dd\#")
End If
End Function

Function fSQLDate1(varDate)
If Not IsNull(varDate) Then
fSQLDate1 = Format(CStr(varDate), "\#yyyy\-mm\-dd\#")
End If
End Function

Is there a reason why this doesn't display anything?

Thanks

Swarv