WildCards wtih Null values (1 Viewer)

woodsy74

Registered User.
Local time
Today, 17:07
Joined
Jul 25, 2012
Messages
26
There’s a form in our db which allows users to run a query for all the records based upon a few fields they’ve input on the form. Below is the query that gets run from the form. It was working fine until I added the last line because they wanted to be able to filter by Product Category. It still works but I am now not getting everything that I used to get. What I found was that a bunch of records have NULL values for Product Category and these records are excluded from the query if the user doesn’t select something for Product Category on the form. So, if the user inputs a value for Team Assigned they’ll get back all of the records which match that Team except for the records where Product Category is NULL.



Do you know how I can update the query so that the NULL Product Category records are also returned when a Product Category is not selected on the form? Let me know if you need any other information. Thanks.



SELECT tbl_OnlineOrders.*

FROM tbl_OnlineOrders

WHERE (((tbl_OnlineOrders.Order_Num) Like "*" & [Forms]![frm_OrderForm]![Text_OrderNumber] & "*")

AND ((tbl_OnlineOrders.Team_Assigned) Like "*" & [Forms]![frm_OrderForm]![Text_Team] & "*")

AND ((tbl_OnlineOrders.Product_Category) Like "*" & [Forms]![frm_OrderForm]![Text_Category] & "*"));
 

plog

Banishment Pending
Local time
Today, 16:07
Joined
May 11, 2011
Messages
11,611
NULL is a tricky beast. Remember in school when they said null isn't 0, its the absence of a value? And everyone was like 'what's the difference' ? This is an application of that.

This is going to sound like rambling, but I guarantee you it makes sense: When you use wildcards you can only do that with something, nothing (NULL) will never make it through. Wildcards mean it has to be like something, even if that something is an empty string (""). But nothing (NULL) isn't even that so it can never make it through that filter because nothing (NULL) isn't even an empty string("").

So, you need to explicitly test for NULL in addition to your criteria. Because I am lazy, let's condense your current criteria to this:

A AND B AND C

You want to test for NULL in addition to C, so you need this:

A AND B AND (C OR CField Is Null)

Most likely you'll want to do that to each existing criteria:

(A OR AField Is Null) AND (B OR BField Is Null) AND (C OR CField Is Null)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:07
Joined
Aug 30, 2003
Messages
36,118
Along the lines of

WHERE (FieldName Like... OR FieldName Is Null) AND (OtherName Like... OR OtherName Is Null)
 

woodsy74

Registered User.
Local time
Today, 17:07
Joined
Jul 25, 2012
Messages
26
So that sort of works. The issue I see is that if the user selects a Product Category on the form they are now returned the records which have that selection plus all of the ones which are null. If the user selected a value for Product Category then I only want to return those and not the nulls. If that's possible? Thanks.
 

woodsy74

Registered User.
Local time
Today, 17:07
Joined
Jul 25, 2012
Messages
26
NULL is a tricky beast. Remember in school when they said null isn't 0, its the absence of a value? And everyone was like 'what's the difference' ? This is an application of that.

This is going to sound like rambling, but I guarantee you it makes sense: When you use wildcards you can only do that with something, nothing (NULL) will never make it through. Wildcards mean it has to be like something, even if that something is an empty string (""). But nothing (NULL) isn't even that so it can never make it through that filter because nothing (NULL) isn't even an empty string("").

So, you need to explicitly test for NULL in addition to your criteria. Because I am lazy, let's condense your current criteria to this:

A AND B AND C

You want to test for NULL in addition to C, so you need this:

A AND B AND (C OR CField Is Null)

Most likely you'll want to do that to each existing criteria:

(A OR AField Is Null) AND (B OR BField Is Null) AND (C OR CField Is Null)
So that sort of works. The issue I see is that if the user selects a Product Category on the form they are now returned the records which have that selection plus all of the ones which are null. If the user selected a value for Product Category then I only want to return those and not the nulls. If that's possible? Thanks.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:07
Joined
Aug 30, 2003
Messages
36,118
You haven't shown exactly what you tried. From the sound of it, you didn't add parentheses as shown/suggested by both of us.
 

plog

Banishment Pending
Local time
Today, 16:07
Joined
May 11, 2011
Messages
11,611
Now we are adding logic to the query. Honestly the best way to handle this is to not open a query but return a Report based on a global data set and then filter the report via DoCmd.OpenReport call:


Using it, you can customize a filter based on whats on the form and the report shows just what the user wants. With that said, what you want is possible just in a query, but it gets tedious:


OrderNumCriteria: IIf(Isnull(Forms!YourForm!OrderNumInput)=False, True, IIf([Order_Num] Like "*" & Forms!YourForm!OrderNumInput & "*", True, False)

Then under that custom field, in its criteria section you put True. So that code most likely doesn't work (syntax error, wrong field names), but the idea I am conveyting is that you test the form for a value, use it in a query if present, otherwise let everything through.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:07
Joined
Feb 19, 2002
Messages
42,976
Using the * as the leading character in a LIKE prevents the database engine from using any index and therefore forces a full table scan. For large tables, this can be slow.

Bottom line is NEVER, EVER use LIKE when you are selecting from a combo because you always have an exact match

To make criteria optional USE

Where (fld1 = forms!yourform!fld1 OR forms!yourform!fld1 IS NULL)
AND (fld2 = forms!yourform!fld2 OR forms!yourform!fld2 IS NULL)
AND (fld3 = forms!yourform!fld13OR forms!yourform!fld3 IS NULL)

pbaldy almost got it right:) It is the form control you check for null, NOT the table field.
 

WayneRyan

AWF VIP
Local time
Today, 21:07
Joined
Nov 19, 2002
Messages
7,122
Unless prevented by software or constraints shouldn't the form control be checked for an empty string and resulting from multiple searches?
On iPhone so can't type well.

Wayne

It changed resulting from multiple to --> righteous mating. LOL
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:07
Joined
Feb 28, 2001
Messages
27,001
If a form control is bound to a table that CAN contain nulls (granted, rare), the form might need NULL checking too. If the form is a SEARCH-ONLY form and talking to an OUTER JOIN type of query, it CAN contain nulls and so needs NULL checking.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:07
Joined
Feb 19, 2002
Messages
42,976
The null check on the form is what allows the criteria to be optional. The original solution put the null check on the table field and that returned rows with null in the criteria field which wasn't what the OP wanted. His reply was --- So that sort of works.
 

Users who are viewing this thread

Top Bottom