Results 1 to 4 of 4

Need help creating a Report from multiple criteria

  1. #1
    Join Date
    Aug 2018
    Posts
    2

    Need help creating a Report from multiple criteria

    I am trying to create a report based on selections from a form. If I remove the Plant and Production line it works (using just the dates) But as soon as I use the rest of the criteria I get blank report.
    Click image for larger version. 

Name:	Selections.jpg 
Views:	13 
Size:	16.5 KB 
ID:	35009



    I used this within the query
    Click image for larger version. 

Name:	Code.jpg 
Views:	13 
Size:	29.0 KB 
ID:	35010

    I someone could direct me to what I am doing wrong. I just get a blank report.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,640
    Several possibilities.
    With your design, every criteria for each field has to exist because this is strictly an AND situation (WHERE Plant = 1 AND Line = 2 AND Date = #01/01/2018#). If you're not understanding or wish to test, open a new test select query on this table and put those values you're showing in verbatim (i.e. don't use form reference) and run it. No records? Try one field at a time. If you get records, then there are none that satisfy all of the conditions you're applying.

    Or if you get records in the test query with all 3 fields with criteria, likely there's a problem with your form/control reference, such as spelling, you're referencing a subform, the form is on a navigation form, or who knows. You may have to put criteria on separate lines (OR condition).

    Or all of the above doesn't apply, and your date field contains time, and there's no records that satisfies the exact interpretation Access does with date fields containing time but the query uses no time values. This is common when those conditions exist plus you use operators on dates (BETWEEN, >=, <=). If you specify BETWEEN 01/01/2018 AND 01/02/2018 for example, the cutoff is midnight of 01/02/2018 when time is not provided. Thus you get nothing after that, such as 08:00. This problem is quite like the aforementioned issue when no records satisfy all the criteria - it's different in that when you look at the data, you expect to get results that match if you don't know how it works.

    EDIT: forgot to mention that Date is a poor choice for any object or field name, plus it would prove to be ambiguous at some point. See
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  3. #3
    Join Date
    Aug 2018
    Posts
    2
    Thank you, I tested this as you suggested, and found that I if I use a text box not a combo box it works perfectly.. THANK YOU

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,640
    Then there might have been an issue with referencing that you inadvertently solved by using a new control. I can't think of why a combo wouldn't work - unless maybe it was bound, which is not what you want when it's providing search criteria. Glad you solved it anyway. You should mark this as solved if you haven't already.

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

Similar Threads

  1. Criteria option not appearing when creating report
    By PATRICKPBME in forum Reports
    Replies: 6
    Last Post: 07-05-2017, 06:12 AM
  2. Replies: 3
    Last Post: 08-05-2015, 11:06 AM
  3. Replies: 1
    Last Post: 12-07-2014, 01:04 PM
  4. Replies: 3
    Last Post: 06-26-2014, 02:02 PM
  5. Replies: 2
    Last Post: 05-09-2011, 05:45 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
  •  
Tech Forums: Microsoft Office Forums