Date not updating correctly

[tractorvix]

Hey Y'all!

I've set up a sub as below:

Private Sub UpdateQRMMaster(Updatedate As Date)
DoCmd.RunSQL "INSERT INTO QRM_MASTER ( Product, UpdateDate, Mat_Date, Bal ) " & _
"SELECT temp.Product, " & Updatedate & " AS UpdateDate, temp.Mat_Date, temp.Bal " & _
"FROM temp;"

End Sub

Variable updatedate is based on user input(tabledate)

Updatedate = DateSerial(Right(tabledate, 2), Mid(tabledate, 3, 2), Left(tabledate, 2))

so for example tabledate = 020708 for 2 June 2008 - this convention is used as I'm importing from daily excel reports which have this naming convention.

soooo....
have stepped through and in my locals window Updatedate = #02/07/08# with type as variant/date

however, when it is populated into the table the field records 30/12/1899

the field data type is set to date/time with format as short date.

Can someone please tell me what I'm doing wrong?

Thanks

 

[Remou]

It is best to use unambiguous dates:

& Format(Updatedate,"yyyy-mm-dd") &

 

[PHV]

SELECT temp.Product, [!]#[/!]" & Updatedate & "[!]#[/!] AS UpdateDate, temp.Mat_Date, temp.Bal " & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886