Results 1 to 5 of 5

Thread: Help with query

  1. #1
    Join Date
    May 2012
    Posts
    33

    Unanswered: Help with query

    So I like to find records by filter from several different options. Basically I have several comboboxes and some check boxes to set up what I like to sort out with this query. I can make each individual without problem, but I cant make it when I combine two ore more.

    So I like to sort out some record based on selection from 3 comboboxes and some dates.
    The criteria for one combobox looks like this:

    PHP Code:
    SELECT GlobalPunchList.PunchIDGlobalPunchList.DisiplineGlobalPunchList.ResponcibleExecutorGlobalPunchList.ProjectNoGlobalPunchList.PunchDescriptionGlobalPunchList.PunchLogGlobalPunchList.ReportedByGlobalPunchList.ReportedDateGlobalPunchList.ClearedDate
    FROM GlobalPunchList
    WHERE 
    GlobalPunchList.Disipline =[Forms]![GlobalPunchList Form]![Combo17] AND [Forms]![GlobalPunchList Form]![Check19]=True); 
    But how to add the other combo and check boxes so that query is depending on check box selection and content of combo + dates?

    Thanks

  2. #2
    Join Date
    May 2012
    Posts
    33
    So Im a bit further, but stil struggeling.

    So the query work with selection from 3 combo's but not sorting between dates. This is my code:

    PHP Code:
    WHERE (GlobalPunchList.Disipline =[Forms]![GlobalPunchList Form]![Combo17] AND [Forms]![GlobalPunchList Form]![Check19]=True OR 
                   
    GlobalPunchList.ResponcibleExecutor =[Forms]![GlobalPunchList Form]![Combo23] AND [Forms]![GlobalPunchList Form]![Check26]=True OR 
                   
    GlobalPunchList.ProjectNo =[Forms]![GlobalPunchList Form]![Combo62] AND [Forms]![GlobalPunchList Form]![Check37]=True) AND 
                   (
    GlobalPunchList.ReportedDate Between [Forms]![New Issue Input Form]![Text45] AND [Forms]![New Issue Input Form]![Text52] AND [Forms]![GlobalPunchList Form]![Check47]=True); 
    So only thouse records between dates should be sorted out. And ofcourse only thouse selected in comboboxes.

    Any tip how to include between dates. The result from this query as it is now is that it ignore between dates but filter out the rest.

    Thanks

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Your SQL doesn't have much in the way of parentheses. A sure sign that you created it in VBA, not in Access' query designer. A pity; that's what the query designer is there for! If you examined your operators (AND/OR) and the various clauses, you would see how deficient the parenthese placing is.

    Put the thing into Access' query designer from scratch (i.e. not with copy and paste from VBA), and let the designer do its design work. You will probably see about 25 pairs of parentheses. You will probably also see that your query will work correctly.

    Sam

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Access will put in way more parenthesis than you really need. Essentially you just need to separate the different OR cases and the final AND

    Something like:

    Code:
    WHERE (  (combo AND check) 
         OR (combo2 and check2) 
         OR (combo3 and check3) )
         AND (date range and check4)
    Notice that each of the individual combo / check OR clauses are inside a set of parenthesis and the entire set of three OR clauses is inside another pair

    The where clause criteria would have to match one or more of the OR clauses AND also the date range / check4 values

    Steve

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I also don't like so many pairs of parentheses. However, I stand by what I said before. The visual display of the query designer and the query-by-example grid might make the programmer aware of a logic error, which he didn't realize when he made the query in VBA.

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •