downhilljon
Registered User.
- Local time
- Tomorrow, 05:42
- Joined
- Jun 14, 2007
- Messages
- 31
Hi there - 1st post so please bear with me!
I have currently got a form set up with unbound combo boxes which draw their list info from seperate tables. My idea was to have a user make a selection from these combo boxes, and then use that data to search (please reference form "Inputs 1b - Generic Search for a Bike" in attached sample file).
When 'Search" is clicked, a query (refer query "Generic Search for Bike") determines the selections from the form, compares it against the master storage table, and I will eventually output it to another form.
My problem lies in the Expression required in the query to read from the input form, but more importantly, in dealing with the Null values if one of the fields is untouched.
The expression I would ultimately like to use is:
IIf(IsNull([Forms]![Inputs 1b - Generic Search for a Bike]![SearchYear]),[YearID] Like "*",[YearID] Like [Forms]![Inputs 1b - Generic Search for a Bike]![SearchYear])
so, if there is a value selected, search with that as the criteria, and if the field has been left blank (Null) then search as if the criteria is a complete wildcard - ie return all results. I have tried many different expressions, with Nz(), Like, etc, to no avail.
I'm baffled, because when I break down the expression into it's component parts and run them as the query (eg the IIF returning simple values, or just the Like [Forms]![Inputs 1b - Generic Search for a Bike]![SearchYear], etc) they all work. It's when the expression is combined that no results are returned from the query.
Incidentally, is there a way of running through the expressions in Expression Builder step by step, as with VBA code or Excel functions?
I'd greatly appreciate any help anyone can provide!
Cheers
I have currently got a form set up with unbound combo boxes which draw their list info from seperate tables. My idea was to have a user make a selection from these combo boxes, and then use that data to search (please reference form "Inputs 1b - Generic Search for a Bike" in attached sample file).
When 'Search" is clicked, a query (refer query "Generic Search for Bike") determines the selections from the form, compares it against the master storage table, and I will eventually output it to another form.
My problem lies in the Expression required in the query to read from the input form, but more importantly, in dealing with the Null values if one of the fields is untouched.
The expression I would ultimately like to use is:
IIf(IsNull([Forms]![Inputs 1b - Generic Search for a Bike]![SearchYear]),[YearID] Like "*",[YearID] Like [Forms]![Inputs 1b - Generic Search for a Bike]![SearchYear])
so, if there is a value selected, search with that as the criteria, and if the field has been left blank (Null) then search as if the criteria is a complete wildcard - ie return all results. I have tried many different expressions, with Nz(), Like, etc, to no avail.
I'm baffled, because when I break down the expression into it's component parts and run them as the query (eg the IIF returning simple values, or just the Like [Forms]![Inputs 1b - Generic Search for a Bike]![SearchYear], etc) they all work. It's when the expression is combined that no results are returned from the query.
Incidentally, is there a way of running through the expressions in Expression Builder step by step, as with VBA code or Excel functions?
I'd greatly appreciate any help anyone can provide!
Cheers
