SQL Date Problem

  • Thread starter Thread starter DS
  • Start date Start date
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)
 
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 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
 
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
 
Back
Top