Insert Statement with Text and Date datatypes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been trying to convert the following to handle text and date
datatypes, unsuccessfully.

This is on the On Click event of a command button on a form.

"INSERT INTO tblQuality (strStaffID, strRating, dtmErrorDate) " & _
"VALUES(" & Me!txtstaffID & ", " & Me!txtrating & "," & Me.dtmErrorDate ")"

I've played with adding single quotes to the test fields and # signs to the
date fields. So far, all I have succeeded at is Syntax errors.

Thank you in advance!
Renee
 
Renee said:
I have been trying to convert the following to handle text and date
datatypes, unsuccessfully.

This is on the On Click event of a command button on a form.

"INSERT INTO tblQuality (strStaffID, strRating, dtmErrorDate) " & _
"VALUES(" & Me!txtstaffID & ", " & Me!txtrating & "," &
Me.dtmErrorDate ")"

I've played with adding single quotes to the test fields and # signs
to the date fields. So far, all I have succeeded at is Syntax
errors.

Thank you in advance!
Renee
Click to expand...


I think you need to continue playing ;-)

"INSERT INTO tblQuality (strStaffID, strRating, dtmErrorDate) " & _
"VALUES('" & Me!txtstaffID & "', '" & Me!txtrating & "', #" & _
format$(Me.dtmErrorDate, "yyyy-mm-dd") & "#)"

In addition to single quotes on text, you neeed # for dates AND
depending on origin, some formatting of the date. The above is
ISO 8601, but here's another format, whith a bit of information
http://allenbrowne.com/ser-36.html

If this doesn't fix it, please also tell us which syntax errors
you are getting, and if possible, what is highlighted in the
errormessage.
 
Thank you RoyVidar!

RoyVidar said:
I think you need to continue playing ;-)

"INSERT INTO tblQuality (strStaffID, strRating, dtmErrorDate) " & _
"VALUES('" & Me!txtstaffID & "', '" & Me!txtrating & "', #" & _
format$(Me.dtmErrorDate, "yyyy-mm-dd") & "#)"

In addition to single quotes on text, you neeed # for dates AND
depending on origin, some formatting of the date. The above is
ISO 8601, but here's another format, whith a bit of information
http://allenbrowne.com/ser-36.html

If this doesn't fix it, please also tell us which syntax errors
you are getting, and if possible, what is highlighted in the
errormessage.
Click to expand...
 
Back
Top