Using inputbox value in a query

[gutmj]

Hi All

What I am trying to achieve is to ask a user for a month number via inputbox and then use this value in update queries - see the below piece of code.

The problem is that I would like ask user once at the beginning and re-use the value provided. However inputbox is called each time 'monthNumber' is used within SQL statement.

How could I solve it? I wouldn't like to use a form as this is the only value required from the user to input. Also I can't use a month() function instead of input box.

Thank you.





Sub updTables()

monthNumber = CInt(InputBox("Enter the month number:"))

DoCmd.RunSQL "UPDATE tblTexDataArchive SET tblTexDataArchive.Month_name = Monthname(monthNumber), tblTexDataArchive.Month_no = monthNumber, tblTexDataArchive.Year_no = Year(Date());"

'subsequent updates

DoCmd.RunSQL "UPDATE"
DoCmd.RunSQL "UPDATE"

End Sub

 

[pbaldy]

You have to concatenate the variable into the string:

"...tblTexDataArchive.Month_no = " & monthNumber & ", ..."

 

[gutmj]

Thanks for the suggestion. Once I've seen it I thought it's going to work. I still get an inputbox once SQL line is executed (now it has a month name as title).

SQL line looks as follows:

sqlString = "UPDATE tbltexDataArchive SET tbltexDataArchive.Month_name =" & MonthName(monthNumber) & ", tbltexDataArchive.Month_no = " & monthNumber & ", tbltexDataArchive.Year_no = " & Year(Date) & ";"

DoCmd.RunSQL sqlString

 

[pbaldy]

If it's asking for Month_name, then that must not be the name of the field. Is it perhaps a space rather than an underscore?

 

[gutmj]

Thanks again. You've put me on the right track.

it should be:

tbltexDataArchive.Month_name =" & "'" & MonthName(monthNumber) &"'" & "

instead of:

tbltexDataArchive.Month_name =" & MonthName(monthNumber) & "

 

[pbaldy]

I noticed that but that should have thrown a syntax error, not a parameter prompt. In any case, you can simplify it:

tbltexDataArchive.Month_name ='" & MonthName(monthNumber) & "', ..."

By the way, I wouldn't bother storing the month name. You can always get it from the month number that you're storing.

 

[gutmj]

Good points. Thanks again