Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    Unanswered: Variables in a SQL query

    Can you include variables in the WHERE clause of a strSQL query in VBA?

    I have the following code:

    txtRefid.SetFocus
    If Not IsNull(txtRefid) Then
    intID = Val(txtRefid.Text)
    End If

    strSQL = "SELECT id, app_code, type, status, log_date, log_by, issue_desc, priority " & _
    "FROM eProc_Issues " & _
    "WHERE id LIKE intID;"

    For some reason, the value that was entered into the txt box is passed into the variable in the if statement, but not in the SQL statement.

    Scotty

  2. #2
    Join Date
    May 2002
    Posts
    19

    Split the statement up

    You need to split the WHERE clause off from the SQL statement.

    "Where ID Like " & intID

    THis should work. Now bear in mind that if you variable is numerical, the statement is fine, however, if it is not numerical, you will need to include your single quotes.

    THis should work

  3. #3
    Join Date
    Jul 2002
    Posts
    3
    I tried the suggested code however now I get an error msg of

    Run-Time error 3075
    Syntax Error (missing operator) in query expression 'id LIKE 1'.

    In the VB window, the following line of code is highlighted in yellow:

    Me.frm_Result.Form.RecordSource = strSQL

    and my strSQL looks like this:

    strSQL = "SELECT id, app_code, type, status, log_date, log_by, issue_desc, priority " & _
    "FROM eProc_Issues " & _
    "WHERE id LIKE" & intID

    What operator am I missing?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually,

    "WHERE id LIKE" & intID

    should be

    "WHERE id = " & intID

    because there's likely (pardon the pun) only one row with that id value

    LIKE is used for character fields, and only if you want some kind of pattern matching

    if you just want to pass in a variable to find the row with that value, use the equal sign and not the LIKE keyword

    if you are testing for equality of a character value, don't forget your single quotes --

    "WHERE app_code = '" & strAppcode & "' "

    rudy
    http://rudy.ca/

Posting Permissions

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