Results 1 to 7 of 7
  1. #1
    radian89 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Dec 2015
    Posts
    37

    Multiple Criteria Query to Generate Report, Result are not satisfying :( Help Please

    Hi All,

    First of All, HAPPY NEW YEAR 2016 .
    I tried to use multiple criteria query to generate report, from a form. but the result wasn't very satisfying, I couldn't write SQL, so i use design view to write the criteria. here's what i want to achieve.

    I have search forms with subforms,

    I can search based on Barcode, Name, Supplier, Category, and from & end date range. here's when i search based on date range
    Click image for larger version. 

Name:	Searchforms.JPG 
Views:	16 
Size:	63.5 KB 
ID:	23233

    and if i choose & combine with the supplier name It'll show it like this
    Click image for larger version. 

Name:	Searchforms2.JPG 
Views:	16 
Size:	43.0 KB 
ID:	23234

    the forms & subforms show no problem at all. and this is what i expected when i print the report. With different query, i want to apply multiple criteria in order to achieve the same filtering in the form, but the result is not very good. here's when i only input date range = OK, the same result

    Click image for larger version. 

Name:	FormsvsQuery.JPG 
Views:	15 
Size:	67.6 KB 
ID:	23235

    when i combine with the supplier name = NOT OK, the query show all data from supplier name with outside of the date range.
    Click image for larger version. 

Name:	FormsvsQuery2.JPG 
Views:	15 
Size:	50.0 KB 
ID:	23236

    here's my query in design view
    Click image for larger version. 

Name:	querydesign.JPG 
Views:	16 
Size:	53.1 KB 
ID:	23237

    after research, i know that my date is in OR, which will show if only 1 of it is true. but how to make it AND OR at the same time? how to write it?


    I also attach the database, the query name is DtbsIncomingStockListForReportingQ, the form name is IncomingStockStatusF

    thanks a lot for the help
    Regards 2016's new spirit



    here the database attachment
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is this what you want?

    SELECT AddStockQ.BarcodeIDItem, AddStockQ.ItemName, AddStockQ.ItemCategory, AddStockQ.SupplierName, AddStockQ.ReceiveDate, AddStockQ.StockQty, AddStockQ.StockNotes
    FROMAddStockQ
    WHERE (((AddStockQ.BarcodeIDItem)=[forms]![IncomingStockStatusF]![BarcodeIDItem] Or (AddStockQ.BarcodeIDItem) Is Null) AND ((AddStockQ.ItemName)=[forms]![IncomingStockStatusF]![CBOItemName] Or (AddStockQ.ItemName) Is Null) AND ((AddStockQ.ItemCategory)=[forms]![IncomingStockStatusF]![CBOCategory] Or (AddStockQ.ItemCategory) Is Null) AND ((AddStockQ.SupplierName)=[forms]![IncomingStockStatusF]![CBOSupplierName] Or (AddStockQ.SupplierName) Is Null) AND ((AddStockQ.ReceiveDate) Between [forms]![IncomingStockStatusF]![StartDate] And [forms]![IncomingStockStatusF]![EndDate] Or (AddStockQ.ReceiveDate) Is Null));


    I never use dynamic parameterized query. I prefer VBA to build filter criteria http://www.allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    radian89 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Dec 2015
    Posts
    37
    Quote Originally Posted by June7 View Post
    Is this what you want?

    SELECT AddStockQ.BarcodeIDItem, AddStockQ.ItemName, AddStockQ.ItemCategory, AddStockQ.SupplierName, AddStockQ.ReceiveDate, AddStockQ.StockQty, AddStockQ.StockNotes
    FROMAddStockQ
    WHERE (((AddStockQ.BarcodeIDItem)=[forms]![IncomingStockStatusF]![BarcodeIDItem] Or (AddStockQ.BarcodeIDItem) Is Null) AND ((AddStockQ.ItemName)=[forms]![IncomingStockStatusF]![CBOItemName] Or (AddStockQ.ItemName) Is Null) AND ((AddStockQ.ItemCategory)=[forms]![IncomingStockStatusF]![CBOCategory] Or (AddStockQ.ItemCategory) Is Null) AND ((AddStockQ.SupplierName)=[forms]![IncomingStockStatusF]![CBOSupplierName] Or (AddStockQ.SupplierName) Is Null) AND ((AddStockQ.ReceiveDate) Between [forms]![IncomingStockStatusF]![StartDate] And [forms]![IncomingStockStatusF]![EndDate] Or (AddStockQ.ReceiveDate) Is Null));


    I never use dynamic parameterized query. I prefer VBA to build filter criteria http://www.allenbrowne.com/ser-62.html
    Hi June,

    I tried the SQL, it doesn't work, it display blanks. my goal is i want to create report based on the search result. my approach is
    search in subform is using different query from query for generated report, so i use 2 query : 1 for the subform, 1 for the report (the query i asked you is for this report).

    is there any other solution to generate report other than this? if not, writing the SQL from allenbrowne, will be the only way...

    thanks

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't understand that.

    None of the records have any blank fields.

    Okay, two query objects with the same filter criteria. What exactly is not working?

    You want the filter criteria to include ALL of the parameters or do you want to allow user to be able to select any combination of parameters?

    I am guessing from your images you want the latter. If your parameters where all for text fields, the use of LIKE operator and wildcard would work. Review http://www.datapigtechnologies.com/f...mtoreport.html

    Since some of your parameters are not for text fields, LIKE operator and wildcard will not work (except it does work on Yes/No field). Would have to provide alternate values if the search controls are left blank.

    AND AddStockQ.ReceiveDate Between Nz([forms]![IncomingStockStatusF]![StartDate], #1/1/1900#) And Nz([forms]![IncomingStockStatusF]![EndDate], #12/31/2900#)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    radian89 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Dec 2015
    Posts
    37
    Quote Originally Posted by June7 View Post
    Don't understand that.

    None of the records have any blank fields.

    Okay, two query objects with the same filter criteria. What exactly is not working?

    You want the filter criteria to include ALL of the parameters or do you want to allow user to be able to select any combination of parameters?
    first question : the 1st query didn't use SQL to filter the criteria, it use VBA code, triggered by click button = the purpose for the form only. the 2nd query, is to generate report from the form, after the user search from it, it using the SQL code, to get the same result filter as the first one = this is the one isn't working properly.

    second question :
    the goal is to allow user to be able to select any combination of parameters.

    thanks a lot

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Edited my previous post while you were reviewing. Look at again.

    As I said, I don't use dynamic parameterized queries. I would use VBA to build filter criteria. Basically the same code used to build filter criteria for form.

    Allen Browne's tutorial addresses this.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    radian89 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Dec 2015
    Posts
    37
    Quote Originally Posted by June7 View Post
    Don't understand that.

    None of the records have any blank fields.

    Okay, two query objects with the same filter criteria. What exactly is not working?

    You want the filter criteria to include ALL of the parameters or do you want to allow user to be able to select any combination of parameters?

    I am guessing from your images you want the latter. If your parameters where all for text fields, the use of LIKE operator and wildcard would work. Review http://www.datapigtechnologies.com/f...mtoreport.html

    Since some of your parameters are not for text fields, LIKE operator and wildcard will not work (except it does work on Yes/No field). Would have to provide alternate values if the search controls are left blank.

    AND AddStockQ.ReceiveDate Between Nz([forms]![IncomingStockStatusF]![StartDate], #1/1/1900#) And Nz([forms]![IncomingStockStatusF]![EndDate], #12/31/2900#)

    Hi June7,

    Thanks a lot for the help, that works perfectly fine. well noted.

    regards,

    Adrian

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

Similar Threads

  1. Replies: 6
    Last Post: 07-03-2015, 02:40 AM
  2. Replies: 7
    Last Post: 09-27-2014, 09:11 PM
  3. Replies: 26
    Last Post: 09-18-2014, 10:18 AM
  4. Replies: 6
    Last Post: 01-12-2014, 03:11 AM
  5. Replies: 6
    Last Post: 05-05-2013, 02:12 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