cwbarrett
asked on
filter by form - exclude null
Hi:
I have a continuous form with a recordsource having 4 text fields total. I'll call the recordsource fields Field1, Field2, .... In the form header I have 4 fields called field1filter, field2filter, ... When a header filter field is Null I want to show all records (with respect to that field), null or not null, but when there is text in the respective filter field I want to show only records that are "like" the filter, without the nulls. If I type text into a second "filter" field, I want to drill down the records to match the 2nd filter criteria as well, only records that match, no nulls with respect to the filtered fields. So, with text in two of the filter fields I want to show only records that have matching text in their respective fields.
The data is originally pulled from an Excel sheet and put into the record source table but I was extremely diligent with my append queries extracting this data from the Excel sheet to make sure I have Nulls and not zero length fields or spaces instead of text. I'm aware of the problems that null vs. 0 length or spaces can cause.
Here is the criteria used in the form's query for the 4 filtered fields:
Field1 criteria: Like "*" & [Forms]![<formname>]![fiel
Field2 criteria: Like "*" & [Forms]![<formname>]![fiel
Field3 criteria: Like "*" & [Forms]![<formname>]![fiel
Field4 criteria: Like "*" & [Forms]![<formname>]![fiel
Basically, with respect to each filter, if I type text into a header filter I want to show matching records without null values, but if the filter is null, I want to show all records, including nulls.
Hope I explained correctly, any help on this is greatly appreciated.
Charlie
I have a continuous form with a recordsource having 4 text fields total. I'll call the recordsource fields Field1, Field2, .... In the form header I have 4 fields called field1filter, field2filter, ... When a header filter field is Null I want to show all records (with respect to that field), null or not null, but when there is text in the respective filter field I want to show only records that are "like" the filter, without the nulls. If I type text into a second "filter" field, I want to drill down the records to match the 2nd filter criteria as well, only records that match, no nulls with respect to the filtered fields. So, with text in two of the filter fields I want to show only records that have matching text in their respective fields.
The data is originally pulled from an Excel sheet and put into the record source table but I was extremely diligent with my append queries extracting this data from the Excel sheet to make sure I have Nulls and not zero length fields or spaces instead of text. I'm aware of the problems that null vs. 0 length or spaces can cause.
Here is the criteria used in the form's query for the 4 filtered fields:
Field1 criteria: Like "*" & [Forms]![<formname>]![fiel
Field2 criteria: Like "*" & [Forms]![<formname>]![fiel
Field3 criteria: Like "*" & [Forms]![<formname>]![fiel
Field4 criteria: Like "*" & [Forms]![<formname>]![fiel
Basically, with respect to each filter, if I type text into a header filter I want to show matching records without null values, but if the filter is null, I want to show all records, including nulls.
Hope I explained correctly, any help on this is greatly appreciated.
Charlie
ASKER
That seems like it would give me the same result but I may not understand your answer. Could you show me how this would look in a query please?
When viewing a set of records in a table and you right-click a field and select "Equals "<currentfieldtext>" the records show only matching records for that field value (no nulls). Then you go to another field and do the same it will show only records where both fields have a match. But if you click "remove filter" in the menu all records show. This is exactly how I want my filters to work except I want to type in the info into filter fields in my form header.
When viewing a set of records in a table and you right-click a field and select "Equals "<currentfieldtext>" the records show only matching records for that field value (no nulls). Then you go to another field and do the same it will show only records where both fields have a match. But if you click "remove filter" in the menu all records show. This is exactly how I want my filters to work except I want to type in the info into filter fields in my form header.
My code includes the 'and' operator - otherwise the same.
I was thinking of a button-triggered event on a form, and code
e.g.
sub buttonclick
dim strcriteria as string
add the above code
me.filter= strcriteria
end sub
let me know is this isn't clear - can you send a copy of the database?
I was thinking of a button-triggered event on a form, and code
e.g.
sub buttonclick
dim strcriteria as string
add the above code
me.filter= strcriteria
end sub
let me know is this isn't clear - can you send a copy of the database?
ASKER
is it possible to place an iif() function in the query criteria like:
iif(isnull([field1filter])
This will create one criteria if the value of the field1filter is null, and another criteria if it is not null. But I don't know the proper way to place this in the query criteria.
Could also "build" a criteria string in the form in a hidden text field called field1filterstring and place "[Forms]![<formname>]![fie
iif(isnull([field1filter])
This will create one criteria if the value of the field1filter is null, and another criteria if it is not null. But I don't know the proper way to place this in the query criteria.
Could also "build" a criteria string in the form in a hidden text field called field1filterstring and place "[Forms]![<formname>]
Here is the way I do it:
Select ...
From ...
Where (field1 = Forms!yourform!filter1 OR Forms!yourform!filter1 Is Null)
AND (field2 = Forms!yourform!filter2 OR Forms!yourform!filter2 Is Null)
AND (field3 = Forms!yourform!filter3 OR Forms!yourform!filter3 Is Null)
AND ....
1. Since the compound expression uses both AND and OR, you MUST use parentheses to correctly control the order of evaluation.
2. This technique works whenever you want to support optional arguments. If one or more of the arguments is not optional, remove the OR part of the expression.
3. This type of formatting is completely messed up by QBE. So, NEVER, EVER save this query from QBE view or you won't be able to read the where clause. When I use queries with this type of complex criteria, I make a copy of the query and label it with an _copy so if I accidentally open the main query with the QBE, I have a backup and won't have to retype the expression so I can read it. Just for yourself, as long as you have a copy of the string formatted as above, go ahead and open it with QBE. Notice all the lines of criteria and how complex they look. Switch to SQL view and see the mess. You'll get burned a few times but don't worry, you can always retype the where clause to be readable.
Select ...
From ...
Where (field1 = Forms!yourform!filter1 OR Forms!yourform!filter1 Is Null)
AND (field2 = Forms!yourform!filter2 OR Forms!yourform!filter2 Is Null)
AND (field3 = Forms!yourform!filter3 OR Forms!yourform!filter3 Is Null)
AND ....
1. Since the compound expression uses both AND and OR, you MUST use parentheses to correctly control the order of evaluation.
2. This technique works whenever you want to support optional arguments. If one or more of the arguments is not optional, remove the OR part of the expression.
3. This type of formatting is completely messed up by QBE. So, NEVER, EVER save this query from QBE view or you won't be able to read the where clause. When I use queries with this type of complex criteria, I make a copy of the query and label it with an _copy so if I accidentally open the main query with the QBE, I have a backup and won't have to retype the expression so I can read it. Just for yourself, as long as you have a copy of the string formatted as above, go ahead and open it with QBE. Notice all the lines of criteria and how complex they look. Switch to SQL view and see the mess. You'll get burned a few times but don't worry, you can always retype the where clause to be readable.
ASKER
I see what you're doing but won't this still show me nulls even with text in the respective form filter? And now I now what you mean by complex and QBE rewriting the entire query on it's own.
I did find a fairly simple solution though. In the Field row of the query, rather than put the table's field name, such as [location], I put this:
LocationNoNull: IIf(IsNull([Location]),"",
This eliminated having to deal with Null results, results were a text string or a zero-length string, no nulls. Then in the criteria row I put this:
Like "*" & [Forms]![ImportMain]![txtF
My original criteria originally had this which was necessary to show all values including null:
Like "*" & [Forms]![ImportMain]![txtF
Not sure how performance will be affected using an "iif" function in the query field but this is a transitional database that will probably have only a few thousand records max at one time.
Thanks for everyone's help.
Charlie
I did find a fairly simple solution though. In the Field row of the query, rather than put the table's field name, such as [location], I put this:
LocationNoNull: IIf(IsNull([Location]),"",
This eliminated having to deal with Null results, results were a text string or a zero-length string, no nulls. Then in the criteria row I put this:
Like "*" & [Forms]![ImportMain]![txtF
My original criteria originally had this which was necessary to show all values including null:
Like "*" & [Forms]![ImportMain]
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Pat, lots of great info here. I see your point on the ZLS, they can be troublesome. Though in this case ZLS was my friend and null was my enemy.
Great point on the placing a function in a query field, I can see where that would cause problems.
I'm in the fire protection industry. The app I made is a utility that draws records from various Excel sheets (linked tables) where I place into one local table. The Excel sheets are from different manufactures each having their own way of identifying their product. A Mfg's identifier might be "SMK", or "SMOKEDET" or "DETECTOR" and my job is to make them all "SMK DET". Another example is "RELAY" (electrical relay). The Identifier "RELAY" doesn't mean much, it's what the relay does that's important. I determine what is does based on keywords in the other 4 fields. I drill down to where I know all items in the list need to be a particular identifier. Then using a query I update the identifier on each of the records to what is should be. Rinse, repeat for other non-valid identifiers till all have a valid identifier.
Then I export to a master database (which is 100k plus records.) where it is related to a particular customer. Then I clear the app's table and do another set of records for another customer.
If it took me an hour to "clean up" an Excel sheet or table prior to developing this app. It now takes me 15 minutes. And, I can train someone to use the app and I won't have to do it all!!
Charlie
Great point on the placing a function in a query field, I can see where that would cause problems.
I'm in the fire protection industry. The app I made is a utility that draws records from various Excel sheets (linked tables) where I place into one local table. The Excel sheets are from different manufactures each having their own way of identifying their product. A Mfg's identifier might be "SMK", or "SMOKEDET" or "DETECTOR" and my job is to make them all "SMK DET". Another example is "RELAY" (electrical relay). The Identifier "RELAY" doesn't mean much, it's what the relay does that's important. I determine what is does based on keywords in the other 4 fields. I drill down to where I know all items in the list need to be a particular identifier. Then using a query I update the identifier on each of the records to what is should be. Rinse, repeat for other non-valid identifiers till all have a valid identifier.
Then I export to a master database (which is 100k plus records.) where it is related to a particular customer. Then I clear the app's table and do another set of records for another customer.
If it took me an hour to "clean up" an Excel sheet or table prior to developing this app. It now takes me 15 minutes. And, I can train someone to use the app and I won't have to do it all!!
Charlie
If you are going to allow ZLS, then it is best to make the field required so it won't ever be Null. ZLS is "something" whereas Null is "nothing".
One other point about working with nulls. When a foreign key can be null, you will have to use Outer Joins. Usually Left but sometimes Right in order to not loose records.
One other point about working with nulls. When a foreign key can be null, you will have to use Outer Joins. Usually Left but sometimes Right in order to not loose records.
ASKER
Thanks, I'm finally understanding Null/ZLS.
i.e. ((like a or a= null) and (like b or b = null) .. etc.
the code would be similar to:
if filter1 <> "" then strcriteria = "Field1 ='" & filter1 & "'"
if filter2 <> "" then strcriteria = strcriteria & " AND Field2 ='" & filter2 & "'"
if filter3 <> "" then strcriteria = strcriteria & " AND Field3 ='" & filter3 & "'"
if filter4 <> "" then strcriteria = strcriteria & " AND Field4 ='" & filter4 & "'"