SQL Date Problem

D

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)
 
D

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\#") & ")"
 
D

DS

Douglas said:
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\#") & ")"
Click to expand...
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
 
D

DS

DS said:
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
Click to expand...
It works now. For whatever reason I had to delete the field and make a
new one. I guess it was corrupted.
Thanks
DS
 
Top