SQL Date Problem

[DS]

I have an SQL statement and the Date field keeps giving me an error. I
have the #, but that doesn't seem to work. What can I be doing wrong?
Thanks
DS

PMSQL = "INSERT INTO PayName
(PaymentNameID,PaymentName,PaymentType,ExpirationDate) " & _
"Values(" & Forms!CouponNames!TxtID & ",'" & Forms!CouponNames!TxtName &
"'," & 3 & ",#" & Forms!CouponNames!TxtDate & "#)"
DoCmd.RunSQL (PMSQL)

 

[Douglas J Steele]

Are you getting an error message? If so, what is it?

If your Short Date format is set dd/mm/yyyy (through Regional Settings),
that'll cause problems correctly recognizing the date, although no error
should be generated. If this is your scenario, use the Format function to
explicitly format the date in mm/dd/yyyy format:

PMSQL = "INSERT INTO PayName " & _
"(PaymentNameID,PaymentName, " & _
"PaymentType,ExpirationDate) " & _
"Values(" & Forms!CouponNames!TxtID & ",'" & _
Forms!CouponNames!TxtName & "'," & _
3 & ", " & _
Format(Forms!CouponNames!TxtDate, "\#mm\/dd\/yyyy\#") & ")"

 

[DS]

Are you getting an error message? If so, what is it?

If your Short Date format is set dd/mm/yyyy (through Regional Settings),
that'll cause problems correctly recognizing the date, although no error
should be generated. If this is your scenario, use the Format function to
explicitly format the date in mm/dd/yyyy format:

PMSQL = "INSERT INTO PayName " & _
"(PaymentNameID,PaymentName, " & _
"PaymentType,ExpirationDate) " & _
"Values(" & Forms!CouponNames!TxtID & ",'" & _
Forms!CouponNames!TxtName & "'," & _
3 & ", " & _
Format(Forms!CouponNames!TxtDate, "\#mm\/dd\/yyyy\#") & ")"

Douglas,
I'm getting this, Insert into Error 3134. Also my Date Format is set to
Short Date. Should I just take off the formating?
Thanks
DS

 

[DS]

Douglas,
I'm getting this, Insert into Error 3134. Also my Date Format is set to
Short Date. Should I just take off the formating?
Thanks
DS

It works now. For whatever reason I had to delete the field and make a
new one. I guess it was corrupted.
Thanks
DS