Search not returning results with a blank entry

[jamierbooth]

Hi Peeps,

I have a switchboard which runs a google style search query to find people based on their first name, surname, date of birth or NI number on clicking the search button. It uses the following criteria in the query Like "*" & [Forms]![SearchF]![Firstname] & "*" for each of the above fields.

This works perfectly until someone inputs a record that doesn't have anything in one of the fields. ie, full name and DoB, but no NI number. When a record is entered in this way, the table stores the record but the search query cannot find it.

Any Ideas?

Jamie.

 

[James Dudden]

Try adding the following:

Like "*" & [Forms]![SearchF]![Firstname] & "*" or Is Null

 

[namliam]

Null values are notoriously hard, they are NULL, i.e. nothing, not filled not empty they are NULL.

If you are wanting to make "good" searches the best way to go about that is to use a filter by form and some code to ammend the where clause to suite your exact needs at the time rather than using some clumsy Like "*" & [Forms]![SearchF]![Firstname] & "*"
Instead of searching for "anything", while you dont want to search that field at all... is the way to go

This question is repeated many times on this forum, so I suggest you go here:
http://www.access-programmers.co.uk/forums/showthread.php?t=255019
where I am going over some stuff with another user very simular to your problem....

Or search for Filter by form and see what you find.

 

[jamierbooth]

Thanks both!

James, your solution worked a treat.
Namliam, I have to create another bigger more complicated version of the DB I'm building at the moment, so I will investigate your advice.

Cheers, Jamie.