SQL Update statement ask for input

  • Thread starter Thread starter Gary L
  • Start date Start date
G

Gary L

Hi, I use variable in SQL statement, but it says "Enter Paramenter" instead.
Here is my statement:
Enbr = CStr(Month(CurM))
If Enbr <= "9" Then
Enbr = "0" + Enbr
End If
Enbr = CStr(Year(CurM)) + "-" + Enbr
DoCmd.RunSQL "Update dailyInvc_tb3 Set LogMonth = Enbr"
Any idea?
Thank you.
GL
 
Enbr is a string, so you have to construct the sql statement differently.Try
this:
Update dailyInvc_tb3 Set LogMonth = " & char(39) & Enbr & chr(39).
This puts single quotes around the string value and adds it to sql statement.

YOu may have
 
Hi, I use variable in SQL statement, but it says "Enter Paramenter" instead.
Here is my statement:
Enbr = CStr(Month(CurM))
If Enbr <= "9" Then
Enbr = "0" + Enbr
End If
Enbr = CStr(Year(CurM)) + "-" + Enbr
DoCmd.RunSQL "Update dailyInvc_tb3 Set LogMonth = Enbr"
Any idea?
Thank you.
GL
Click to expand...

You need to concatenate the enbr value into the string and enclose the
string value with single quotes as the double quote is already in use.
"Update ... etc ... Set LogMonth = '" & Enbr & "'"

However, it seems you are jumping through hoops to enter a 2 digit
string and then precede a 4 digit year (2007-09) to it into a field.

Is CurM a valid Date datatype?
Try:

DoCmd.RunSQL "Update dailyInvc Set LogMonth = '" &
Format([CurM],"yyyy-mm") & "';"

Better yet, to avoid the warning messages (unless you first
SetWarnings False, then True), use:

CurrentDb.Execute "Update .... etc ... ;", dbFailOnError
 
Back
Top