Results 1 to 5 of 5
  1. #1
    Jerry215 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    2

    Where clause of SQL in access VBA

    Thanks in advance for your help. I have 3 controls on a form for the user to enter parameters for the query. Control # 1 is for the WONO, control #2 is for SegNo1 and control #3 is for SegNo2. These fields are in the query, but not in the sql because they aren't needed except to get the correct records. I have code in place to verify these are filled in. these inputs are in the where clause of the sql. Here is the SQL:

    strSQL1 = "SELECT Code1 & PN & Code2 & Desc & Code3 & " & _
    "TQY & Code4 & Code5 & Code6 & Code7 " & _
    "FROM PartsListQ " & _
    "WHERE (((PartsListDesc.WONO)=[Forms]![ConversionF]![WONO]) AND ((PartsListDesc.WOSGNO)=[Forms]![ConversionF]![SegNo1] Or (PartsListDesc.WOSGNO)=[Forms]![ConversionF]![SegNo2]))"

    When this code runs I get an error code: Too few parameters. Expected 3. When run in the immediate window all 3 parameters are there as entered in the form.

    Using just the SegNo1 in the code works perfectly:

    strSQL1 = "SELECT Code1 & PN & Code2 & Desc & Code3 & " & _
    "TQY & Code4 & Code5 & Code6 & Code7 " & _
    "FROM PartsListQ " & _
    "WHERE PartsListDesc.WONO = """ & Forms!ConversionF!WONo & _


    """ AND PartsListDesc.WOSGNO = """ & Forms!ConversionF!SegNo1 & """"

    So the question is how do I enter the SegNo2 into the code so it works? I have tried a lot of different ways and nothing is working so far. I really need to allow the user to enter 2 different segment numbers to get the records needed in 1 query.
    Jerry215

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You should check each control individually, to determine the 'where'.
    alter the query with the criteria....


    Code:
    '----------------
    sub btnOpenQry_click()
    '----------------
    dim sWhere as string 
    dim qdf as querydef
    
    
    sWhere = " where 1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    
    
    set qdf = currentdb.querydefs("qsMyQry")
    qdf.sql = "Select * from table " & swhere
    qdf.close
    
    
    docmd.openquery qdf.name
    
    
    end sub

  3. #3
    Jerry215 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    2
    Ok. This is what i did:
    Dim myWONO As String, mySegNo1 As String, mySegNo2 As String

    myWONO = [Forms]![ConversionF]![WONo] ' Get the work order number.
    mySegNo1 = [Forms]![ConversionF]![SegNo1] ' Get the first segment number.
    mySegNo2 = [Forms]![ConversionF]![SegNo2] ' Get the second segment number.

    sWhere = " where 1=1"
    If Not IsNull(myWONO) Then sWhere = sWhere & " and [WONO]='" & myWONO & "'"
    If Not IsNull(mySegNo1) Then sWhere = sWhere & " and [SegNo1]='" & mySegNo1 & "'"
    If Not IsNull(mySegNo2) Then sWhere = sWhere & " and [SegNo2]='" & mySegNo2 & "'"

    Set db = CurrentDb()
    strSQL1 = "SELECT Code1 & PN & Code2 & Desc & Code3 & " & _
    "TQY & Code4 & Code5 & Code6 & Code7 " & _
    "FROM PartsListQ " & sWhere

    I am getting an error code: Too few parameteres. 2 expected.
    What did i do wrong?
    Thank you for your help
    Jerry215

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to concatenate all those fields into one long string?

    Desc has special meaning in SQL, enclose in [] and see if that helps: [Desc].

    Are those 3 fields all text data type? If not, remove the apostrophe delimiters for number type.

    A string variable cannot hold Null, only Variant type can hold Null. The code should trigger run-time error if the controls/fields are Null.

    Either declare the variables as Variant (if type not explicitly declared Variant is default) or deal with possibility of Null and test for empty string instead of Null.

    myWONO = [Forms]![ConversionF]![WONo] & ""

    If myWONO <> "" Then
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As already suggested by June, the error
    Too few parameters. 2 expected.
    indicates that two of your three datatypes are numbers NOT text

    Check carefully and change from text to number delimiters
    e.g. if SegNo1 is a number, use
    Code:
    [SegNo1]=" & mySegNo1 & "
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-11-2016, 03:49 AM
  2. Access VBA docmd where clause
    By rstrout in forum Programming
    Replies: 16
    Last Post: 03-09-2014, 11:50 AM
  3. does Access have WITH clause?
    By johnseito in forum Access
    Replies: 1
    Last Post: 09-23-2013, 11:00 PM
  4. Access 2007 OVER clause workaround
    By MrSmith in forum Queries
    Replies: 3
    Last Post: 07-19-2013, 12:08 PM
  5. WHERE clause
    By fabiobarreto10 in forum Forms
    Replies: 5
    Last Post: 04-12-2012, 02:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums