Something on this order --
Like IIF([tblStore].[Store_Name] Is Null, "*", [tblStore].[Store_Name])
But I do not think it will give you what you want. If the field does not
match a record in [tblStore].[Store_Name] then it will pull the record.
Therefore it does not matter if [tblStore].[Store_Name] has a matching record
or not.
Put your conditions into words. If the field does not match any record in
[tblStore].[Store_Name] what do you want to happend.
--
KARL DEWEY
Build a little - Test a little
alex said:
It depends on how you want the filter to work. As written,
Like [tblStore].[Store_Name]
Only records where {Store_Name] is an exact match will be returned.
It could also be writeen as
= [tblStore].[Store_Name]
and return the same results.
So, if what you want is =Store_Name or Null use
= [tblStore].[Store_Name] Or IS NULL
If you want matches that start with the value in Store_Name or Null
Like [tblStore].[Store_Name] & "*" OR IS NULL
I have a query that uses a table's field as the criteria. Something
like:
Like [tblStore].[Store_Name]
If there's a value in Store_Name the query works fine (they're are 4
other table fields as criteria as well). But if the value is null, it
doesn't work.
I tried Like [tblStore].nz([Store_Name],"*") but Access doesn't like
it.
any thoughts?
alex- Hide quoted text -
Thanks for the comments!
After looking at the function, is there a way to check for a null
value first. If null exists use no criteria, else use criteria.
Something like: iff( [tblStore].[Store_Name] is null, no criteria,
[tblStore].[Store_Name] )
I cannot use Or Is Null because I get erroneous results.- Hide quoted text -
- Show quoted text -
Hi Karl, thanks for the response.
I have (e.g.,) a query with many fields.
Two of the fields use a table for criteria. Like [tblStore].
[Store_Name] and Like [tblStore].[Location].
The two fields in tblStore can both have values, one can have a value
and one null, or both null.
Let's say for example, I use the following: Like [tblStore].
[Store_Name] and Like [tblStore].[Location]...
if tblStore.Location has a value and tblStore.Store_Name does not, I
must insert "*" into the criteria of tblStore_Name or insert a "*" in
the actual table.
Now let's say I erase Like [tblStore].[Store_Name] from my
criteria...the query works fine! That's why I was hoping to use the
IIF function to evaluate the table first (which it will do) and if the
value is Null to not use any criteria, and if not null to use the
value in the table.
I was actually able to use Like IIF([tblStore].[Store_Name] Is Null,
"*", [tblStore].[Store_Name]) as you mentioned above. It worked, but
what happens when both values are null, then the criteria will be Like
"*" and Like "*". That will give me everything!
alex