locked
Where Clause of VBA SQL RRS feed

  • Question

  • Thanks in advance for your help. I have been working on this for a while and still haven't got it right. I posted this a couple of weeks ago, but for some reason i couldn't get back into this forum. So i will start a new thread.

    I have an access database with linked tables to an SQL server. I am trying to retrieve records in a query from inputs entered by the user on a form.  There are 3 controls on the form that must be filled in by the user. The first control is for the WONO, the second control is for the SegNo1, the third control is for the SegNo2.  The following code works perfect:

    Dim myWhere as String

    myWhere = "WHERE PartsListDesc.WONO = """ & Forms!ConversionF!WONo & """ AND PartsListDesc.WOSGNO = """ & Forms!ConversionF!SegNo1 & """"     Note: this is not multiple lines. This window did the word wrap.

        strSQL1 = "SELECT Code1 & PN & Code2 & Descript & Code3 & " & _
            "TQY & Code4 & Code5 & Code6 & Code7 " & _
            "FROM PartsListQ " & myWhere

    When i add code for the SegNo2 i get an error message: ODBC--Call Failed.

    myWhere = "WHERE PartsListDesc.WONO = """ & Forms!ConversionF!WONo & """ AND PartsListDesc.WOSGNO = """ & Forms!ConversionF!SegNo1 & """ Or PartsListDesc.WOSGNO = """ & Forms!ConversionF!SegNo2 & """"

    When this is run in the immediate window it shows all of the correct inputted information. What am i doing wrong?

    Thank you for the help. Jerry215

    Monday, March 12, 2018 2:11 PM

Answers


  • Did you test it?

    ' This goes to a standard module.

    Create a new standard module or open one, where you place you helper methods.

    Syntax error in FROM clause

    Yup, typo.

    Public Sub Test()
    ' Were you're using your actual code.
    
      Dim Sql As String
      Dim Where As String
    
    ' Use the correct data types.
    ' Cause we need only to escape strings.
      Dim Wono As String
      Dim SegNo1 As String
      Dim SegNo2 As String
    
      SegNo1 = "A1" 'Forms!ConversionF!SegNo1
      SegNo2 = "A2" 'Forms!ConversionF!SegNo2
      Wono = "A3" 'Forms!ConversionF!Wono
    
      Where = _
        "WHERE P.WONO = " & SqlQuote(Wono) & " AND P.WOSGNO = " & SqlQuote(SegNo1) & ";"
    
      Where = _
        "WHERE P.WONO = " & SqlQuote(Wono) & " AND (P.WOSGNO = " & SqlQuote(SegNo1) & " OR P.WOSGNO = " & SqlQuote(SegNo2) & ");"
    
      Sql = _
        "SELECT P.Code1 & P.PN & P.Code2 & P.Descript & P.Code3 & P.TQY & P.Code4 & P.Code5 & P.Code6 & P.Code7 " & _
        "FROM PartsListQ P " & _
        Where
      
      Debug.Print Sql
    
    End Sub

    There was obviously the space after the alias name missing.


    • Edited by Stefan Hoffmann Tuesday, March 13, 2018 12:50 PM Wrong image.
    • Marked as answer by Jerry215 Tuesday, March 13, 2018 1:06 PM
    Tuesday, March 13, 2018 12:48 PM

All replies

  • Thanks in advance for your help. I am trying to combine 2 fields for the criteria for the where clause of my SQL.

    The Where clause:
    "WHERE PartsListDesc.WONO = """ & Forms!ConversionF!WONo & _
           """ AND PartsListDesc.WOSGNO = """ & Forms!ConversionF!SegNo3 & """"
          
    The where clause works perfectly.

    I have 2 controls on the form for the user to input the 2 segment numbers they want to see: SegNo1 and SegNo2. The user can enter 1 seg number in SegNo1 or can enter 2 seg numbers by entering a number in SegNo1 and SegNo2.

    mySegNo1, mySegNo2, mySegNo1A and mySegNo2A are all Strings.

    My Code:

    ' mySegNo1 = Me!SegNo1
    ' mySegNo2 = Me!SegNo2

    ' If (Eval("Forms!ConversionF!SegNo2 Is Null")) Then
    '     Me!SegNo3 = [mySegNo1]
    ' Else
    '     mySegNo1A = ("PartsListDesc.WOSGNO= " & mySegNo1)
    '     mySegNo2A = ("PartsListDesc.WOSGNO= " & mySegNo2)
    '     Me!SegNo3 = [mySegNo1A] & " Or " & [mySegNo2A]
    ' End If

    Me!SegNo1 and Me!SegNo2 will always be a number 01 through 99. If the user only puts a number in the first field it works   perfectly, if they enter 2 numbers it doesn't work (1 in each field).

    If I put this in the criteria for the field WOSGNO in the query PartsListDesc ("any number" Or "any number") it works             perfectly. It looks like this:

    WHERE (((PartsListDesc.WONO)="9895635") AND ((PartsListDesc.WOSGNO)="30" Or (PartsListDesc.WOSGNO)="40")); this works perfectly.

    So how do I combine the 2 fields SegNo1 & SegNo2 to put into Me!SegNo3 so the Where clause will work?
    I have tried many different combinations and nothing i have tried work.

    Thank you for your help.

    Jerry215

    Wednesday, February 28, 2018 1:03 PM
  • I don't see how you are using the WHERE clause. Is this for a full SQL statement to be used somewhere or is it for the FILTER on the form or what? So my attempt below may not fit what you need.

    You have quotes around values that you say  area always numbers? I have put those in this code too, but I assume the fields are defined as strings. If not, you could remove those quotes and make it easier to read

    I tried to put something together, based on processes I always use, maybe it will help.

        ' Put the part that is always there in the where clause into the variable first
        strWhere = "WHERE PartsListDesc.WONO=""9895635"") "
        
        ' If the first number is not null
        If Nz(myseqno1, "") <> "" Then
            ' Concatenate the "AND" connector with the field name an the variable that has the value
            strWhere = strWhere & " AND PartsListDesc.WOSGNO =""" & mySeqNo1A & """"
        End If
        
        ' If the second number is not null
        If Nz(myseqno2, "") <> "" Then
            ' Modify the existing where statement to add a left parenthesis in front of the first part of the OR statement to group it together
            ' this statement will replace " AND " with " AND ("
            strWhere = Replace(strWhere, " AND ", " AND (")
            
            ' Now add the second part of the OR statement, with it's closing parenthesis
            strWhere = strWhere & " OR PartsListDesc.WOSGNO)=""" & myseqno2 & """)"
        End If
        
        ' Add the closing semicolon to whatever SQL statement was created
        strWhere = strWhere & ";"
    


    Larry

    Wednesday, February 28, 2018 1:36 PM
  • Sorry, i didn't mean for this to be so confusing.  This is the where clause copied directly out of the actual query SQL with "30" Or "40" entered directly in the criteria of the WOSGNO field .  I did not copy the rest of the SQL - just the where clause. This works perfectly when entered like this directly in the query field criteria. This is the actual where clause with   "30" Or "40"   entered in the criteria:

    WHERE (((PartsListDesc.WONO)="9895635") AND ((PartsListDesc.WOSGNO)="30" Or (PartsListDesc.WOSGNO)="40"));

    Below is the Where clause from the SQL in my VBA code. As before I did not copy the rest of the SQL.

    "WHERE PartsListDesc.WONO = """ & Forms!ConversionF!WONo & _
           """ AND PartsListDesc.WOSGNO = """ & Forms!ConversionF!SegNo3 & """"

    The WONO also comes from a control on the ConversionF form, this part works and the code works if there is only a number in SegNo1 control. The problem is combining the values from both controls - SegNo1 & SegNo2.

    SegNo1 cannot be null, i have code to check this before trying to use this so no need to check it again.

    the code to check if SegNo2 is null is to setup SegNo3 for the where clause with both values from SegNo1 & SegNo2.

    So how do i combine the 2 controls, SegNo1 and SegNo2 and put them in SegNo3 so this where clause works as written - which is looking at SegNo3 for the criteria.

    WHERE PartsListDesc.WONO = """ & Forms!ConversionF!WONo & _
           """ AND PartsListDesc.WOSGNO = """ & Forms!ConversionF!SegNo3 & """"

    Thank you for your help.

    Wednesday, February 28, 2018 2:40 PM
  • Looking at that last where statement, it looks like you are trying to compare 2 values but only specifying the field name once. That would build an invalid statement, since you have to specify the field name twice.

    These statements from your original code:

    mySegNo1A = ("PartsListDesc.WOSGNO= " & mySegNo1)
    mySegNo2A = ("PartsListDesc.WOSGNO= " & mySegNo2)
    Me!SegNo3 = [mySegNo1A] & " Or " & [mySegNo2A]

    Would produce:

    PartsListDesc.WOSGNO= 123 OR PartsListDesc.WOSGNO= 456

    The WHERE statement you say works has quotes around the numbers (indicating the field WOSGNO is defined as a string. So you need to put those quotes in 1A and 2A too.

    mySegNo1A = ("PartsListDesc.WOSGNO= """ & mySegNo1 & """")
    mySegNo2A = ("PartsListDesc.WOSGNO= """ & mySegNo2 & """")

    Then you need to make sure there is a value in 2A before concatenating it to No3

    If NZ(mySeqNo2,"") = "" Then

          Me!SegNo3 = [mySegNo1A]

    Else

          Me!SegNo3 = [mySegNo1A] & " OR " & [mySeqNo2A]

    End if


    Larry

    Wednesday, February 28, 2018 3:33 PM
  • Note: this is not multiple lines. This window did the word wrap.

    Just use the <> button in the toolbar to embed code. This gives you even syntax highlighting.

    Clean up your code. Everything in one lines is not always a good idea. Especially as we don't want implicit conversions. Also what is the table name? PartsListQ or PartsList? Cause it looks like an alias problem as your using PartsListDesc in the where predicate.

    And I guess there is a logic error in the operator precedence.

    So it should look like this:

    Option Compare Database
    Option Explicit
    
    Public Function SqlQuote(AText As String, Optional ADelimiter As String = "'") As String
    ' This goes to a standard module.
    ' Escape text for concatenating SQL fragments or statements.
    
      SqlQuote = ADelimiter & Replace(AText, ADelimiter, ADelimiter & ADelimiter) & ADelimiter
    
    End Function
    
    Public Sub Test()
    ' Were you're using your actual code.
    
      Dim Sql As String
      Dim Where as String
    
    ' Use the correct data types.
    ' Cause we need only to escape strings.
      Dim Wono As String
      Dim SegNo1 As String
      Dim SegNo2 As String
    
      SegNo1 = Forms!ConversionF!SegNo1
      SegNo2 = Forms!ConversionF!SegNo2
      Wono = Forms!ConversionF!WONo
    
      Where = _
        "WHERE P.WONO = " & SqlQuote(Wono) & " AND P.WOSGNO = " & SqlQuote(SegNo1) & ";"
    
      Where = _
        "WHERE P.WONO = " & SqlQuote(Wono) & " AND (P.WOSGNO = " & SqlQuote(SegNo1) & " OR P.WOSGNO = " & SqlQuote(SegNo2) & ");"
    
      Sql = _
        "SELECT P.Code1 & P.PN & P.Code2 & P.Descript & P.Code3 & P.TQY & P.Code4 & P.Code5 & P.Code6 & P.Code7 " & _
        "FROM PartsListQ P" & _
        Where
    
    End Sub

    Monday, March 12, 2018 3:21 PM
  • I don't understand, Please explain this:

    Public Function SqlQuote(AText As String, Optional ADelimiter As String = "'") As String
    ' This goes to a standard module.
    ' Escape text for concatenating SQL fragments or statements.

      SqlQuote
    = ADelimiter & Replace(AText, ADelimiter, ADelimiter & ADelimiter) & ADelimiter

    End Function

    What is this code doing? What are the ADelimiters?

    Tuesday, March 13, 2018 11:47 AM
  • I am getting an error:

    Run Time Error '3131'

    Syntax error in FROM clause.

    Tuesday, March 13, 2018 12:21 PM

  • Did you test it?

    ' This goes to a standard module.

    Create a new standard module or open one, where you place you helper methods.

    Syntax error in FROM clause

    Yup, typo.

    Public Sub Test()
    ' Were you're using your actual code.
    
      Dim Sql As String
      Dim Where As String
    
    ' Use the correct data types.
    ' Cause we need only to escape strings.
      Dim Wono As String
      Dim SegNo1 As String
      Dim SegNo2 As String
    
      SegNo1 = "A1" 'Forms!ConversionF!SegNo1
      SegNo2 = "A2" 'Forms!ConversionF!SegNo2
      Wono = "A3" 'Forms!ConversionF!Wono
    
      Where = _
        "WHERE P.WONO = " & SqlQuote(Wono) & " AND P.WOSGNO = " & SqlQuote(SegNo1) & ";"
    
      Where = _
        "WHERE P.WONO = " & SqlQuote(Wono) & " AND (P.WOSGNO = " & SqlQuote(SegNo1) & " OR P.WOSGNO = " & SqlQuote(SegNo2) & ");"
    
      Sql = _
        "SELECT P.Code1 & P.PN & P.Code2 & P.Descript & P.Code3 & P.TQY & P.Code4 & P.Code5 & P.Code6 & P.Code7 " & _
        "FROM PartsListQ P " & _
        Where
      
      Debug.Print Sql
    
    End Sub

    There was obviously the space after the alias name missing.


    • Edited by Stefan Hoffmann Tuesday, March 13, 2018 12:50 PM Wrong image.
    • Marked as answer by Jerry215 Tuesday, March 13, 2018 1:06 PM
    Tuesday, March 13, 2018 12:48 PM
  • Stefan Hoffmann
     you are my HERO!!  This worked perfectly!!! Thank you!
    Tuesday, March 13, 2018 1:05 PM