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):
Code:
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:
Code:
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.
