Filtering forms with Parameter Queries based on value in one combo

[LeLe]

Our employees often filter our customer data base by either city or zip code.
I have set up an unbound control for each on the customer form. The
underlying query uses the following 2 expressions.

IIf(IsNull([forms]![CompaniesSplitView]![FltCityCbx]),"
",[Forms]![CompaniesSplitView]![FltCityCbx]) to filter based on the value
selected in the FltCityCbx or

IIf(IsNull([forms]![CompaniesSplitView]![FltZipCbx]),"
",[Forms]![CompaniesSplitView]![FltZipCbx]) to filter based on the value
selected in the FltZipCbx.

Either one of these works great if it is the only expression in the query,
but I can't seem to get either of them working if both on are the query.

I think I understand why, but I don't know how to fix it. I believe it has
to do with the Null value, since neither city or zip is ever Null. I think
what I need to do is elimate the city filter when the user select zip, and
eliminate the zip filter when the user selects city. Can I do this in my
query by revising the expression?

thanks so much

 

[Douglas J. Steele]

Instead of what you've got, use

[forms]![CompaniesSplitView]![FltCityCbx] OR
([Forms]![CompaniesSplitView]![FltCityCbx] IS NULL)

and

[forms]![CompaniesSplitView]![FltZipCbx] OR
([Forms]![CompaniesSplitView]![FltZipCbx] IS NULL)

as your criteria.

With what you've currently got, when a particular combo box has nothing
selected in it, you're searching for the literal string " ".

 

[LeLe]

Works perfect!!! Thanks so much!

Instead of what you've got, use

[forms]![CompaniesSplitView]![FltCityCbx] OR
([Forms]![CompaniesSplitView]![FltCityCbx] IS NULL)

and

[forms]![CompaniesSplitView]![FltZipCbx] OR
([Forms]![CompaniesSplitView]![FltZipCbx] IS NULL)

as your criteria.

With what you've currently got, when a particular combo box has nothing
selected in it, you're searching for the literal string " ".


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Our employees often filter our customer data base by either city or zip
code.
I have set up an unbound control for each on the customer form. The
underlying query uses the following 2 expressions.

IIf(IsNull([forms]![CompaniesSplitView]![FltCityCbx]),"
",[Forms]![CompaniesSplitView]![FltCityCbx]) to filter based on the value
selected in the FltCityCbx or

IIf(IsNull([forms]![CompaniesSplitView]![FltZipCbx]),"
",[Forms]![CompaniesSplitView]![FltZipCbx]) to filter based on the value
selected in the FltZipCbx.

Either one of these works great if it is the only expression in the query,
but I can't seem to get either of them working if both on are the query.

I think I understand why, but I don't know how to fix it. I believe it
has
to do with the Null value, since neither city or zip is ever Null. I
think
what I need to do is elimate the city filter when the user select zip, and
eliminate the zip filter when the user selects city. Can I do this in my
query by revising the expression?

thanks so much

 

[LeLe]

I must be a glutton for punishment... I have now added a third combo bax this
time based on customer NAME. It (and the other two combo boxes) work just
fine using the expression you recommended. Now I am wondering if I can
change the NAME combo box to filter even if the user enters wildcards. In
our industry many of our customers use some of the same words in their names.
I would like the user to enter *Sew* and have the filter find "Sew it
Seams", "Sew What", "Sew Fine", "Miss Sew and Sew"

Can I do this?
Thanks again

 

[John W. Vinson]

I must be a glutton for punishment... I have now added a third combo bax this
time based on customer NAME. It (and the other two combo boxes) work just
fine using the expression you recommended. Now I am wondering if I can
change the NAME combo box to filter even if the user enters wildcards. In
our industry many of our customers use some of the same words in their names.
I would like the user to enter *Sew* and have the filter find "Sew it
Seams", "Sew What", "Sew Fine", "Miss Sew and Sew"

If you're searching for wildcards, use a Textbox rather than a combo box, and
use a criterion of

LIKE "*" & Forms!YourForm!txtFindName & "*"

 

[LeLe]

I can't seem to get it to work. Here is the criteria as I entered it: Like
"*" & [Forms]![CompaniesSplitView]![FltName] & "*"

Each time I run it all my records appear. There is no filtering. What am I
doing wrong?

Thanks again.