Printable View
I am trying to be able to search my database, using a form, but i have one major problem, i have to fill in every feild in the querey.
Hoe can i have it so when i dont want to search by price, i leave it blank, and it is ignored during the query
My current to search with the feild is [Forms]![Search]![Price]
But how can i ignore the feild if it is Null? i tried [Forms]![Search]![Price] or is null but it doesnt work.
Any Ideas
Paradox
Hmm,
Not quite clear what you are trying to do. It seems like you have multiple fields that you want to be able to search by?
Are you dynamically building your sql statement or do you have a stored procedure/saved query that you are calling?
Sory, i forgot to say this is in access.
I have about 10 feilds in my form relating to 10 feilds in a table
I am trying to search using multiple feilds, which i can do, but i have to fill in every feild for the search or it will not work.
for example, if i had only 2 feilds i could search :
Name: adam
Age: 14
and it will give me the record with adam aged 14, but if i searched
Name: Adam
Age : (Leave it blank)
this wouldnt give me any results because it is looking for a record with the name adam and the age ""
What i want it to do is, when i leave one feild blank, it will ignore that during the query. so if i searched for adam it would come up with adam ages 14, and if i searched for only 14 it would come up with adam 14.
If that clearer?
I have fould the following bit of code that is exactly what i want, it keeps giving me the error of
"The Expression you entered has a function containing the wrong number of arguments"
How can i stop this?
VB Code:
Looks like you are missing the end bracket of the Len function.
IIF(Len([forms]![search]![Island]=0),"*", [forms]![search]![Island] & "*")