using NZ in criteria expression

Hello,

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

Like Nz([tblStore].[Store_Name],"*")
--
TedMi

"alex" wrote:

Hello,

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

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

"alex" wrote in message
...
Hello,

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

On Oct 29, 10:04*am, "Klatuu" wrote:
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

"alex" wrote in message

...



Hello,

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 -

- Show 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.

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" wrote:

On Oct 29, 10:04 am, "Klatuu" wrote:
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

"alex" wrote in message

...



Hello,

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 -

- Show 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.

On Oct 29, 12:21*pm, KARL DEWEY
wrote:
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" wrote:
On Oct 29, 10:04 am, "Klatuu" wrote:
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

"alex" wrote in message

....

Hello,

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 -

- Show 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

You did not answer the question --- 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" wrote:

On Oct 29, 12:21 pm, KARL DEWEY
wrote:
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" wrote:
On Oct 29, 10:04 am, "Klatuu" wrote:
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

"alex" wrote in message

....

Hello,

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 -

- Show 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

On Oct 29, 1:16*pm, KARL DEWEY
wrote:
You did not answer the question --- 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" wrote:
On Oct 29, 12:21 pm, KARL DEWEY
wrote:
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" wrote:
On Oct 29, 10:04 am, "Klatuu" wrote:
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

"alex" wrote in message

....

Hello,

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 -

- Show 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- Hide quoted text -

- Show quoted text -

My apologies Karl,

I found a solution to my problem, but I will answer your question...

If the field does not match any record in [tblStore].[Store_Name] what do you want
to happend.

I want the query to return 0 records!

Keep in mind, however; I have other fields in my query referencing
other fields in [tblStore]. They're all on the same line (so it's
And, And, And, etc).

Should [tblStore].[Store_Name] be Null, the rest of the query will
fail! In [tblStore], a field is left empty because the user wants
everyting and anything returned from that field AS LONG AS the other
criteria (in other fields) exist.

A simple solution to this problem (not the solution I mentioned above)
is to instruct the user to insert "*" in any Null field. Programming
this is simple enough; my real problem is that 3 queries referenced
[tblStore] so the fields (in groups of 5) needed to act independently;
i.e., in group 1 (if field.1 and field.2 and field.3 and field.4 and
field.5 are null, do nothing; else if any filed value in group null
then the other fields must have "*" in order for the query to work
properly, then apply that same logic to the other groups of fields in
[tblStore].) In my query criteria I cannot say: Like [tblStore].
[Store_Name] or is Null because I don't want the Null values
independent of the other criteria.

Sounds confusing I'm sure. My solution was to place a query in
between my main queries and the tbl. The in between queries only pull
one particular group, then I use Like iif([tbl.1].[field.1] is null,
"*",[tbl.1][field.1]).

Thanks for your help. Without you guys I'd be sweepin' floors.
alex