Results 1 to 6 of 6
  1. #1
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162

    How many lines of alternate query criteria can I put on query grid.


    I have a query which takes parameter vales from a form's text boxes and combo boxes. But sometimes I choose to keep some of the text/combo boxes blank while running the query and not always same text/combo boxes are filled up. It varies depending on my required result. So I have used "*" wildcard search in most of field's criteria. But used IsNull option for 2 fields where the field values are numeric, because if I use wildcard for them the result may be erroneous. But the problem occurs when I try to keep 2 fields alternate criteria IsNull. It seems access doesn't support more than one field's alternate option as IsNull.
    Am I missing something here??

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    At the moment the best I can answer is you don't use isNull, you use is null

    if this does not help, rather than describe the problem, provide some example data, the sql you are using, the outcome you are getting and the outcome you want.

  3. #3
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by Ajax View Post
    At the moment the best I can answer is you don't use isNull, you use is null
    Sorry Ajax, for confusing you with the misspelling here of Is Null. But the spelling was correct in the database.
    The problem I am facing is with two fields.
    For the BankValue I tried to put the following criteria-
    Between [Forms]![Cons Filter]![txtBank1] And [Forms]![Cons Filter]![txtBank2].
    But when I leave it blank the query doesn't give any result.
    Also for FK_SupplierID field I need to keep the Is Null option in the Or criteria line same as you can see is provided for FK_BrandID field.
    [Forms]![Cons Filter]![cboSupplierID].
    Database is attached herewith.
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    When filter criteria gets complicated, recommend VBA to build. Review http://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.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is the query SQL as formatted by PoorSQL for clarity.
    Code:
    SELECT Cons.Import_ID
    	,[_CategoryDescriptionLookup].Description
    	,[_BrandLookup].Brand_Name
    	,[_supplierLookup].SuppName
    	,Cons.BigQty
    	,Cons.SmallQty
    	,Cons.BankValue
    	,Cons.ActuValue
    	,[_cnfLookup].CNF_Name
    	,Cons.FK_SupplierID
    FROM _cnfLookup
    INNER JOIN (
    	_CategoryDescriptionLookup INNER JOIN (
    		_supplierLookup INNER JOIN (
    			Cons INNER JOIN _BrandLookup ON Cons.[FK_BrandID] = [_BrandLookup].Brand_ID
    			) ON ([_supplierLookup].Supp_ID = Cons.FK_SupplierID)
    			AND ([_supplierLookup].Supp_ID = Cons.FK_SupplierID)
    		) ON [_CategoryDescriptionLookup].Catg_ID = [_BrandLookup].FK_CategoryID
    	) ON [_cnfLookup].CNF_ID = Cons.FK_CNFID
    WHERE (
    		(
    			(Cons.Import_ID) LIKE "*" & [Forms] ! [Cons Filter] ! [cboFiscalYear] & "*"
    			AND (Cons.Import_ID) LIKE "*" & [Forms] ! [Cons Filter] ! [cboStation] & "*"
    			AND (Cons.Import_ID) LIKE "*" & [Forms] ! [Cons Filter] ! [cboCatagory] & "*"
    			)
    		AND (([_CategoryDescriptionLookup].Description) LIKE "*" & [Forms] ! [Cons Filter] ! [cboDes] & "*")
    		AND (
    			(Cons.BankValue) BETWEEN [Forms] ! [Cons Filter] ! [txtBank1]
    				AND [Forms] ! [Cons Filter] ! [txtBank2]
    			)
    		AND ((Cons.FK_SupplierID) = [Forms] ! [Cons Filter] ! [cboSupplierID])
    		AND ((Cons.FK_BrandID) = [Forms] ! [Cons Filter] ! [cboBrandID])
    		)
    	OR (
    		(
    			(Cons.Import_ID) LIKE "*" & [Forms] ! [Cons Filter] ! [cboFiscalYear] & "*"
    			AND (Cons.Import_ID) LIKE "*" & [Forms] ! [Cons Filter] ! [cboStation] & "*"
    			AND (Cons.Import_ID) LIKE "*" & [Forms] ! [Cons Filter] ! [cboCatagory] & "*"
    			)
    		AND (([_CategoryDescriptionLookup].Description) LIKE "*" & [Forms] ! [Cons Filter] ! [cboDes] & "*")
    		AND (
    			(Cons.BankValue) BETWEEN [Forms] ! [Cons Filter] ! [txtBank1]
    				AND [Forms] ! [Cons Filter] ! [txtBank2]
    			)
    		AND (([Forms] ! [Cons Filter] ! [cboBrandID]) IS NULL)
    		);

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry Ajax, for confusing you with the misspelling here of Is Null
    "IS" is a SQL operator (like = is an operator). So Is Null is for sql use.
    IsNull is a function, so misspelling probably doesn't apply here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 5
    Last Post: 08-25-2018, 04:55 PM
  2. Replies: 6
    Last Post: 12-10-2015, 02:39 PM
  3. Display alternate column in a query
    By zero3ree in forum Access
    Replies: 6
    Last Post: 07-02-2015, 12:17 PM
  4. Need Grid lines on null fields in report
    By lpkorn423 in forum Reports
    Replies: 1
    Last Post: 12-27-2012, 08:45 AM
  5. Grid lines vs Grid Dots
    By dharriet in forum Access
    Replies: 0
    Last Post: 10-14-2008, 09:17 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