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\#") & ")"
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
It works now. For whatever reason I had to delete the field and make a
new one. I guess it was corrupted.
Thanks
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Percent Problem 2
Complex If Statement 6
SQL NULL 2
SQL Synatx Incorrect 6
Suspicious SQL 6
Select From 1
Function or Variable Expected 6
SQL SYNTAX ERROR 8

Top