Simple Query Problem Please Help, its urgent!

[iParadox]

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

[Besoup]

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?

[vb_dba]

Are you dynamically building your sql statement or do you have a stored procedure/saved query that you are calling?

[iParadox]

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?

[iParadox]

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?

IIF(Len([forms]![search]![Island]=0,"*", [forms]![search]![Island] & "*")

[brucevde]

Looks like you are missing the end bracket of the Len function.

IIF(Len([forms]![search]![Island]=0),"*", [forms]![search]![Island] & "*")