Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45

    Query with variable number of input parameters

    Grateful for any advice on the syntax of a query where a user can input one, or several parameters to a query that will search a data-set.



    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you use a form, user can pick items on the form like combo boxes, or list boxes, etc...
    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
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks for your reply.

    I don't think that I can use combo or list boxes as there are thousands of potential parameter values.

    I'm quite new to Access, can the query you detailed above be built using the Query Design grid?

    Thanks

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ranman's approach should work perfectly well.
    However, if you want to use a query instead, look at the example I just posted in this similar thread where the OP also wants to use a query instead of code
    https://www.accessforums.net/showthread.php?t=72021
    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

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would help if you could/would be more specific in your question.....

    Quote Originally Posted by keith2511 View Post
    a user can input one, or several parameters to a query that will search a data-set.Thanks
    Do you mean you want to search one field for one or more parameters (ex. search City for "Bath", "Birmingham", "Bradford", "Bristol", "Cambridge", "Canterbury", "Carlisle")
    or you want to search many fields for one (respective) parameter? (ex LastName = "Smyth", FirstName = "Conrad", City = "Leeds")

  6. #6
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks for your interest Steve.

    I want a query where a user can select any of a number of search criteria fields. These fields will be free text. Ideally I'd like to use the Query Design grid to build the query.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by keith2511 View Post
    I want a query where a user can select any of a number of search criteria fields. These fields will be free text.
    Not so sure about the "free text". Would you give examples of search fields and criteria?


    Have you seen this" http://www.allenbrowne.com/ser-62.html
    Code is here http://www.allenbrowne.com/ser-62code.html

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'm now confused by answers given since my previous post
    Do you mean something like the screenshot below where the search term is checked against several fields (& in this case in two subforms)
    Click image for larger version. 

Name:	Capture.PNG 
Views:	17 
Size:	35.2 KB 
ID:	34036
    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

  9. #9
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Hi Steve,

    Thanks for your reply. I think the code that you linked to is exactly what I need. But I'm unsure of how to tailor it to my needs.

    In the example code:

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFilterMainName) Then
    strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
    End If

    If I had say 5 fields that could potentially be searched (FirstName, MainName, Town, County, Country) would I need to declare a criteria string for each one?

    What does the "Me" mean in the code "Me.txtFilterMainName"?

    Is each IF statement nested within the first IF statement i.e.

    If Not IsNull(Me.txtFilterMainName) Then
    strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
    If Not IsNull(Me.txtFilterTown) Then
    strWhere1 = strWhere1 & "([Town] Like ""*" & Me.txtFilterTown & "*"")"


    End If

    End If

    Thanks again.

  10. #10
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Hi Colin,

    No, I just want to give the user the flexibility to search on one or more fields, where they enter a single value for one or more fields

    Thanks

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Maybe we're at cross purposes but that's what my example is doing. The highlighting is just a 'bonus'.
    The links to allen Browne's site provided by ssanfu should be exactly what you need
    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

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by keith2511 View Post
    What does the "Me" mean in the code "Me.txtFilterMainName"?
    "ME" is a reference to the form... a "shortcut", if you will.
    Instead of writing "Forms!Form1.txtFilterMainName", you can use "Me.txtFilterMainName". The "Me" syntax can only be used in a form module.
    I use the "Me" syntax because it is easier to tell whether the value is from a form or a VBA variable.
    -----------------------------------------------------------------------


    Quote Originally Posted by keith2511 View Post
    If I had say 5 fields that could potentially be searched (FirstName, MainName, Town, County, Country) would I need to declare a criteria string for each one?
    Using code, you only need to create criteria if there are values in the controls.
    -----------------------------------------------------------------------

    Quote Originally Posted by keith2511 View Post
    Is each IF statement nested within the first IF statement i.e.
    No. Generally not.
    Code:
    If Not IsNull(Me.txtFilterMainName) Then
       strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
    End If
    
    
    If Not IsNull(Me.txtFilterTown) Then
        strWhere1 = strWhere1 & "([Town] Like ""*" & Me.txtFilterTown & "*"")"
    End If

    -----------------------------------------------------------------------
    One other thing. I've had problems using a test like
    Code:
    If Not IsNull(Me.txtFilterMainName) Then
    If there is a space in the control, you get a false positive. So I've been using
    Code:
    If Len(Trim(NZ(Me.txtFilterMainName,""))) > 0  Then
    From the inside out,
    the NZ function converts NULLs to an empty string.
    Then the Trim function removes leading and trailing spaces.
    Lastly, the Len function checks to see if the count of characters is greater than 0.


    -----------------------------------------------------------------------
    Note that the code is enclosed with code tags. When you reply, in the reply menu, you see the "#". That is the button to add the code tags to indicate the code and to keep formatting.

    You can click the code button (#) then paste in the code or you can paste the code in the reply, select the code, then click on the code button (#).

  13. #13
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks again Steve, you've been a great help.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Steve

    Why do you suggest using
    Code:
    If Len(Trim(NZ(Me.txtFilterMainName,""))) > 0  Then
    rather than
    Code:
    If Nz(Me.txtFilterMainName,"") <>"" Then
    The latter has never failed for me
    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

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Colin

    I'd had problems with code like
    Code:
    If Not IsNull(Me.txtFilterMainName) Then
    or
    Code:
    If Not IsNull(Me.txtFilterMainName) Or (Me.txtFilterMainName = "") Then
    because there was one or more spaces in the control.
    So I started using the Len and Trim functions.


    I've also seen it written
    Code:
    If Len(Me.txtFilterMainName & vbNullString) > 0 Then
    but I prefer to have the Trim function included to deal with any spaces...... (belts and suspenders/braces)

    Another variation using vbNullString instead of the NZ function:
    Code:
    If Len(Trim(Me.txtFilterMainName & vbNullString)) > 0  Then

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. 2 questions on input parameters
    By accessmatt in forum Queries
    Replies: 3
    Last Post: 07-14-2014, 08:11 PM
  2. Requery input parameters syntax?
    By hinsdale1 in forum Forms
    Replies: 15
    Last Post: 05-01-2013, 11:56 PM
  3. Replies: 5
    Last Post: 05-18-2012, 07:31 AM
  4. input parameters
    By jassie in forum Access
    Replies: 1
    Last Post: 01-29-2012, 05:40 PM
  5. Input parameters
    By GloriaLuz in forum Reports
    Replies: 0
    Last Post: 11-14-2005, 09:38 PM

Tags for this Thread

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