If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filtering forms with Parameter Queries based on value in one combo
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 |
#2
|
|||
|
|||
Filtering forms with Parameter Queries based on value in one combo
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 http://I.Am/DougSteele (no private e-mails, please) "LeLe" wrote in message ... 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 |
#3
|
|||
|
|||
Filtering forms with Parameter Queries based on value in one c
Works perfect!!! Thanks so much!
"Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no private e-mails, please) "LeLe" wrote in message ... 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 |
#4
|
|||
|
|||
Filtering forms with Parameter Queries based on value in one combo
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 "LeLe" wrote: 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 |
#5
|
|||
|
|||
Filtering forms with Parameter Queries based on value in one combo
On Sun, 12 Jul 2009 18:12:01 -0700, LeLe
wrote: 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 & "*" -- John W. Vinson JVinson *at* Wysard Of Info *dot* com |
#6
|
|||
|
|||
Filtering forms with Parameter Queries based on value in one c
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. "John W. Vinson" wrote: On Sun, 12 Jul 2009 18:12:01 -0700, LeLe wrote: 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 & "*" -- John W. Vinson JVinson *at* Wysard Of Info *dot* com |
Thread Tools | |
Display Modes | |
|
|