Strange NULL problem with a "Like" statement

[GrandMasterTuck]

[SOLVED] Strange NULL problem with a "Like" statement

Hi guys. I have a database with a table, a query and a form. tblCustomers, frmSearch and qrySearch. tblCustomers has [name] and [num]. frmSearch has three controls: two fields called [sname] and [snum] and a button that opens qrySearch. qrySearch has the same two fields as tblCustomers, but has their criteria set as follows (using [num] as an example):

Like "*" & [Forms]![frmSearch].[snum] & "*"

This allows me to return records even if I only type PART of the value of the field. For instance, I have a record with [name] set to "JONES", and if I go on frmSearch and type "JO" into [sname], Jones's record will show up on the query.

The problem is that, for some of the records in the table, I might not have a value in the [num] column, and those records won't show up if I leave [snum] blank (null). If I leave [sname] or [snum] blank, I want ALL the records, but if I type something, I want ONLY the records wherein any part of the column matches what I typed, and I DO NOT want the nulls.

I tried to modify the code to include the null values so that they all show up if I leave [snum] blank, like this:

Like "*" & [Forms]![frmSearch].[snum] & "*" Or Is Null

But then the problem is that the null ones show up when I type something into [snum]. For instance, I have four records, "Jones" with [snum] = "12345", "Smith" with [snum="67890", "Williams" with [snum]="5678" and "Thompson" with [snum] blank. If I use the first example and leave both fields blank, I don't see Thompson in the search results, but I want to. And if I use the second example and leave them blank, I see everybody, but if I type "5" into [snum], I get Jones, Williams and Thompson, but I only want to see Jones and Williams.

Can anybody tell me what I'm doing wrong? This has me stumped.

EDIT: Here's what I want, worded differently:

IF [sname] and [snum] are both null on frmSearch, return all records in tblCustomers with ANY name and ANY number, including all NULL values in either column.
IF [sname] is NOT null and [snum] is null, return all records where any part of [name] matches [sname] but NOT INCLUDING any NULL [name] records, and ALL [num] values including nulls.
IF [sname] is null but [sname] is NOT null, return all records of all names including nulls where any part of [num] matches [snum] but NOT INCLUDING null [snum] records.
IF [sname] and [snum] are BOTH NOT null, return ONLY those records where any part of [name] matches [sname] AND any part of [num] matches [snum] AND NEITHER [name] or [num] is null.

 

[CJ_London]

try

Like "*" & [Forms]![frmSearch].[snum] & "*" Or [Forms]![frmSearch].[snum] Is Null

 

[GrandMasterTuck]

I'll be darned! That works! Thanks CJ_London... though I'll be effed if I can figure out WHY it works... I would have never guessed to write it that way. Heck, I was writing these weird and complicated IIF statements in that query and kept getting errors saying the statement was too complicated to parse. I was going crazy!

You da man, sir!

 

[Brianwarnock]

Well GMT I hate to see a man effed so here goes
What you are doing is testing to see if a field matches the first part of the criteria or if the CRITERIA Is Null.

If the criteria Is Null then it is true for all records so all records will be selected for that criterion.

This a standard approach for multi criteria selection when not all criteria need be entered.

Hope this helps

Brian

 

[GrandMasterTuck]

Thanks, Brianwarnock. Creating more detailed database-type applications with Access can sometimes twist my brain into a pretzel, and I occasionally need somebody to come along and kick me in the side of the head to clear the dust.

That bit about testing the validity of the criteria "or if the CRITERIA is Null" is what was smearing my glasses. There's about a thousand ways you have to learn to write these things, and I tried about 999 different ways, but never tried sticking the OR statement just before the second iteration of my string and including the "Is Null" statement on the end. I kept thinking the OR had to go right before the "Is Null" for some reason, and tried about twenty permutations of that. I was getting to the point where I was going to pass the [sname] and [snum] values to TempVars because I started thinking it was a problem with frmSearch and the fields therein.

Thanks again you guys. I love this site so much!