Results 1 to 10 of 10
  1. #1
    abenny is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    5

    multiple search criteria in query not working


    I am trying to have a query search the vendor hotline log to find all records that have either an open or closed status between a specific date range. It seems the date range is working but it's bringing up the status portion. what is missing? I attached a copy of the query

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    please don't post updates in threads where it's obvious that you're solving a problem one step at a time and only post when you're frustrated.

    thanks!

    The way I look at this is that you'll eventually solved it on your own, and very quickly too. here, in 1/2 hr. you got one portion solved: https://www.accessforums.net/program...lts-13277.html

    or so it seems.

  3. #3
    abenny is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    5
    I am frustrated, I've been trying to resolve this issue for a week now & only when I looked again did I see the query itself wasn't looking at status & can't see why

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This statement is not clear to me: "It seems the date range is working but it's bringing up the status portion." What do you mean by 'it's bringing up the status portion'? Why shouldn't it 'bring up' the status portion?

    Can there be status other than Open or Closed? If not, this criteria is unnecessary. Is Status field a Yes/No or Text datatype?

    Go to the Query Designer SQL View and copy/paste the SQL string into post for analysis.

  5. #5
    abenny is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    5
    The status selection is not being shown as an option to choose in the query. There is open or closed but I am trying to have the user select either Open or Closed or both & the report results select only those that the user chose.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay still confused with "The status selection is not being shown as an option to choose in the query." but then you go on to say you want user to choose from these options.

    So, if I do understand, you want a paramater prompt. Instead of "Open" or "Closed" in the criteria cell, try Nz([Enter 'Open' or 'Closed'],"*"). Winging it here, never done that. Not sure if blank reponse is null or empty string. I don't like parameter prompts. It is too hard to validate user entry. What if they accidentally type 'Colsed'? The query will still run but no results. Check this article http://office.microsoft.com/en-us/ac...010096314.aspx

    I prefer to have user enter choice on a form then open a form or report based on unfiltered query and filter the form/report with WHERE argument in DoCmd.OpenForm or DoCmd.OpenReport using values from the form.

  7. #7
    abenny is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    5
    They don't type in the status selection on the user form. They can select either open, closed or both. So not sure if that will work

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What user form? You have been referring to only a query so far. Maybe this will help you http://www.datapigtechnologies.com/f...earchform.html

  9. #9
    abenny is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    5
    The "Search Criteria Form" is the user form. The Query is what gives the results from the criteria chosen from the user. See the attachment of the form

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That link I posted is a tutorial for setting up a search form. It shows how to set up the query to handle no choice made by user. Will find this about halfway into the video. Use slide control to get there quick. Did it help you?

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

Similar Threads

  1. Search By Criteria - Flexible Criteria Fields
    By lilanngel in forum Access
    Replies: 0
    Last Post: 03-16-2011, 06:25 PM
  2. Search with multiple criteria
    By injanib in forum Forms
    Replies: 1
    Last Post: 01-16-2011, 11:51 PM
  3. Search Button with Multiple Criteria
    By injanib in forum Forms
    Replies: 2
    Last Post: 01-12-2011, 02:21 AM
  4. Replies: 3
    Last Post: 10-13-2010, 03:35 PM
  5. Replies: 0
    Last Post: 04-08-2010, 12:22 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