Results 1 to 5 of 5
  1. #1
    crawfish124 is offline Novice
    Windows 8 Access 2013
    Join Date
    May 2014
    Posts
    2

    Question Using Multiple Fields in Form to Generate Query


    Right now I have just one table in Access which holds different information for colleges. Some of the fields include the tuition and the state in which it is located. I created a form off of this table that allows me to select any criteria of those fields and run a query to get the related results. For example, I can search by schools in New York that cost 30k and get a list of all associated colleges. I do this by creating a query and having the criteria equal whatever value was selected in the form for both of those properties.

    However, I need some guidance in setting up how to do the same filter of only one of the fields is filled in. So if I want to not select a state but get all schools costing 30k, how can I do that. The only way I can get it to work is selecting a value for both fields. By leaving one blank my query does not return anything. Eventually this will expand to have four or five filters so I would use this knowledge to do same setup if 1,2,3,4 or all 5 filters are used.

    Thanks for any help, if you have any questions let me know.

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    How are you using the search form? Is the criteria in the qry? Do you a default value for the fields on your search form? See this post.

    https://www.accessforums.net/access/...0-a-19299.html
    Last edited by burrina; 05-05-2014 at 10:57 PM. Reason: Link Provided

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It sounds like you would need code to check each control, determine if it is used, if so, add it to the Sql,

    if not isnull(txtName1) then vFilter = VFilter & [Name]='" & txtName & "'"
    if not isnull(cboState2) then vFilter = VFilter & [State]='" & cboState2 & "'"
    if len(vFilter)>0 then vFilter = " WHERE " & vFilter
    sSql = sSql & vFilter
    until finally docmd.openSQL sSql

  4. #4
    BlueIshDan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    8
    https://www.accessforums.net/code-re...orm-44282.html

    This will build a form for you to go by

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you can use like "*" & [forms]![formname]![fieldname] & "*" as your criteria, this basically creates a wildcard search for any non populated value.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-11-2014, 11:14 AM
  2. Replies: 8
    Last Post: 08-02-2012, 10:50 AM
  3. Replies: 2
    Last Post: 06-10-2012, 01:10 PM
  4. Replies: 12
    Last Post: 05-07-2012, 12:41 PM
  5. Form Based Query for Multiple Fields
    By sureelsaraf in forum Access
    Replies: 0
    Last Post: 03-28-2011, 06:14 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