Creating parameter queries based on a form with multiple criteria

[lexy1210]

I am designing a database to hold recipes and associated information. I
would like users to be able to use a form to run their own queries based on a
number of possible criteria. For example I would like them to be able to use
one or more of the fields on the form to search the database i.e. they could
search 'Recipe Name' for those which contain 'Chicken' or they could search
'Recipe Name' for 'Chicken' and 'Recipe Category' for 'Soup' (combo box) and
'Suitable for Freezing' = 'Yes' (checkbox) to return all recipes that match
all three criteria.

I have created a form and an associated query with the following criteria
Like "*" & [forms]![Basic Package1]![Recipe Name] & "*" to search the Recipe
Name with a wildcard
[forms]![Basic Package1]![Recipe Category] Or [forms]![Basic
Package1]![Recipe Category] Is Null for searching the Catgegory combo box
[forms]![Basic Package1]![Suitable fo Freezing] for searching the check box

The query runs perfectly when only one criteria is entered in the query
builder but when all three are entered the query returns all records even if
you only enter data in one of the fields on the form.

How can I design the query so that it will ignore blank fields on the form
(where the user may not want specify) and only return records that match the
user specified criteria.

I hope I've explained myself clearly, I am very new to this! Any help
appreciated.

 

[OfficeDev18 via AccessMonster.com]

It's pretty straightforward, actually. Try something like this:

Private sub form_afterupdate()

dim strSQL as String, LenStr as Long

strSQL = ""
if RecipeName.Value > "" Then
strSQL = "InStr(RecipeName,'" & RecipeName.Value & "') > 0 "
LenStr = Len(strSQL)
EndIf
If RecipeCat.Value > "" Then
If LenSTR > 0 Then
strSQL = strSQL + " And " etc, etc.

I really have to run, or I'd be happy to continue in longhand. Do you see
where I'm going with this?

HTH

I am designing a database to hold recipes and associated information. I
would like users to be able to use a form to run their own queries based on a
number of possible criteria. For example I would like them to be able to use
one or more of the fields on the form to search the database i.e. they could
search 'Recipe Name' for those which contain 'Chicken' or they could search
'Recipe Name' for 'Chicken' and 'Recipe Category' for 'Soup' (combo box) and
'Suitable for Freezing' = 'Yes' (checkbox) to return all recipes that match
all three criteria.

I have created a form and an associated query with the following criteria
Like "*" & [forms]![Basic Package1]![Recipe Name] & "*" to search the Recipe
Name with a wildcard
[forms]![Basic Package1]![Recipe Category] Or [forms]![Basic
Package1]![Recipe Category] Is Null for searching the Catgegory combo box
[forms]![Basic Package1]![Suitable fo Freezing] for searching the check box

The query runs perfectly when only one criteria is entered in the query
builder but when all three are entered the query returns all records even if
you only enter data in one of the fields on the form.

How can I design the query so that it will ignore blank fields on the form
(where the user may not want specify) and only return records that match the
user specified criteria.

I hope I've explained myself clearly, I am very new to this! Any help
appreciated.

 

[lexy1210]

Thanks for this but I have to admit this seems a bit beyond my experience.
Where would I enter this? I was hoping to avoid SQL where possible, perhaps
I need to do more reading. Any pointers or more advice on my original
question would be appreciated. Don't you hate it when you know what you want
to do but you don't know how to do it!!!