Query criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a contacts table that has some blank fields, except contactID. Some
records entered only have an email address or a phone number. If I include
phone number in teh Query form, only the minimum number of records show up,
since not all records have the same info. I realize the query cant search
for something when there is nothing to search for. Do I have to put in 0's
or "no data" or something for the search work properly?

I've tried Nz, and Is Null and so on, and I cant get anything to work. The
newsgroup has given me lots of optional expressions, unfortunately I just
cant get it.

Result needed:
An working expression!!! Company, First name, Last Name, Email address,
phone number search options and if they are blank, return the ones that are
matching.

Are there other reasons why records would not show (besides deleting them)?
Perhaps some weird dynaset or the method of data entry?

Many many thanks in advance!
 
Hi,


If a field appears "blank", its value is probably NULL. The WHERE clause
of the SQL statement may looks like:


.... WHERE (myField1 = 445 OR myField1 IS NULL) AND (myField2='oh' OR
myField2 IS NULL)


to keep record if myField1 = 445 or if it is "blank", and so on.



You can also use

.... WHERE Nz(myField1, 445) = 445 AND Nz(myField2, 'oh') = 'oh'


which save some typing.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top