Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple SQL Question (O2K - Access - VBA)

    I know this is a simple SQL question but I'm relatively new to SQL and it's driving me nuts! Here goes:

    I have coded a SQL statement in a VBA function as follows:
    *******************************************
    strSQL = "UPDATE [Combined-total] SET [Combined-total].[KOB SHIFT] = [KOB SHIFT]+1" & _
    " WHERE ((([Combined-total].MEMBER_NUM) = [tempnum]) AND (([Combined-total].MONTH)= [tempmon]));"

    DoCmd.RunSQL strSQL
    *************************************************

    Tempnum and tempmon are variables that are set prior to the execution of the SQL command. I want it to use the variables as set in the code but it keeps prompting me for input. How do I get VBA to use the variables already set?

    TIA.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Simple SQL Question (O2K - Access - VBA)

    You can't use variables in SQL, you can only use parameters, functions, field values and calculated values. The query engine is interpreting your reference to variables as parameters and asking you for the value you want to use.

    However, since you're building the SQL in code, you can concatenate the variable value into the SQL statement like this:

    strSQL = "UPDATE [Combined-total] SET [Combined-total].[KOB SHIFT] = [KOB SHIFT]+1" & _
    " WHERE ((([Combined-total].MEMBER_NUM) = " & tempnum & ") AND (([Combined-total].MONTH)= " & tempmon & "));"

    Of course, this assumes that both tempnum and tempmon are numeric variables.
    Charlotte

  3. #3
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple SQL Question (O2K - Access - VBA)

    Charlotte:

    Thank you so very much for the prompt, accurate response.

    One of the variables (tempmon) was a string variable and so I had to add a few double quote marks. I did manage to figure that out myself!

    Thanks, again. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •