Search Problems (1 Viewer)

[Stroggy]

Hi from a new member.
It seems that searching for data is one of the main problems people have with Access, and I'm no exception so, here goes.
I've made an unbound form containing multiple fields, including combo boxes, to enter the search strings. A button runs the query that filters the data using the following criteria: -
Like "*" & [Forms]![FrmFlavoursFullSearch].[Form]![CodeSearch] & "*"

I’ve added a similar line each field to search and if I leave a search field blank it still finds all the records, so far so good. Now the problem:-
If one of the search fields in a record is blank it completely misses out that record, even if I wasn't searching that field. Is there a way to include records containing blank fields in the search results?
Many thanks
Stroggy

 

[namliam]

Yes there is a way, however... the problem is not in fixing this, the issue is your doing this half way.

If you dont want to search for something (ie. return everything/leave CodeSearch blank) then your sql shouldnt include it.

This solution really goes kaput if you start getting more and more fields to search on, instead you should use a 'proper' search form that builds the sql as per need... Something like so:

If isnull(Me.CodeSearch) then
    mySQL = mySQL & " and Yourfield like '*" & me.CodeSearch & "*' "
end if

Have a search for "search form" I think / expect that will return some good samples for you to explore and addapt to your situation.

 

[Stroggy]

Thanks namliam,
The user will be searching different combinations of different data in different fields and my sql abilities are not up to this task
I've attached a basic example of what I'm trying to achieve.
Run the form FrmFlavoursFullSearch
There are 11 records but, the 3 with empty fields don't display.
I have tried using IIF and Is Null with no success.
I will have another search of the forum for more search tips.
Stroggy

 

[namliam]

Try looking up this:
http://www.access-programmers.co.uk/forums/showthread.php?t=188663&highlight=search+form
http://www.access-programmers.co.uk/forums/showthread.php?t=159756&highlight=search+form
http://www.access-programmers.co.uk/forums/showthread.php?t=121973&highlight=search+form
http://www.access-programmers.co.uk/forums/showthread.php?t=99353&highlight=search+form
http://www.access-programmers.co.uk/forums/showthread.php?t=103312&highlight=search+form

If you run into specific questions I am more than happy to try and answer them for you.