SQL Update statement ask for input

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
 
M

mray29

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
 
F

fredg

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

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
 

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

Top