Criteria testing fields that may contain null values

[AAVF]

Hi

We have a problem with a query.

An Access database links via ODBC to a UNIX server. To speed things, we use
the ODBC to load the relevant tables to the local PC that runs Access so
that all querying is done locally.

One of the reports we run allows the user to list all invoices within a
period. They are also allowed to select a customer code and a product set on
which to base the report. The product set comprises a Warehouse code (a
system grouping classification rather than a physical building) and a
product code. There will always be something in the field [PRODUCT_CODE],
but it is possible for the [WAREHOUSE] field to be null.

We used to run the database in Access 97, and I am pretty sure the following
criteria expression worked, in that if the user hit 'enter' when prompted
for the Warehouse, all records would be returned, including those with
'null' in the [WAREHOUSE] field. About six months ago we converted this
rather large database (c200 queries and reports) to Access 2000. Today, my
users tell me this report is ignoring records that have Warehouse value.

The criteria string used on the [WAREHOUSE] field is as follows

Like [Warehouse or enter for all] & "*"

The variable [Warehouse or enter for all] is then assigned to a created
field of its own, [WAREHOUSE_SELECTED], to show on the report.

It appears that searching using a wildcard ignores null values, but we need
to be able to pick up the nulls if the user wants all records returned.

Any ideas on how to fix this?

Regards

Tom Millington

 

[Ken Snell]

Try this criteria expression:

Like [Warehouse or enter for all] & "*" OR [Warehouse or enter for all] Is
Null

 

[Pieter Linden]

http://www.mvps.org/access/queries/qry0001.htm

 

[AAVF IT]

Thanks Ken

That worked a treat.

Has something changed from A97 to A2000? I'm sure the string I was using
worked on A97.

Tom

Try this criteria expression:

Like [Warehouse or enter for all] & "*" OR [Warehouse or enter for all] Is
Null

--
Ken Snell
<MS ACCESS MVP>

Hi

We have a problem with a query.

An Access database links via ODBC to a UNIX server. To speed things, we use
the ODBC to load the relevant tables to the local PC that runs Access so
that all querying is done locally.

One of the reports we run allows the user to list all invoices within a
period. They are also allowed to select a customer code and a product

set

on
which to base the report. The product set comprises a Warehouse code (a
system grouping classification rather than a physical building) and a
product code. There will always be something in the field [PRODUCT_CODE],
but it is possible for the [WAREHOUSE] field to be null.

We used to run the database in Access 97, and I am pretty sure the following
criteria expression worked, in that if the user hit 'enter' when prompted
for the Warehouse, all records would be returned, including those with
'null' in the [WAREHOUSE] field. About six months ago we converted this
rather large database (c200 queries and reports) to Access 2000. Today, my
users tell me this report is ignoring records that have Warehouse value.

The criteria string used on the [WAREHOUSE] field is as follows

Like [Warehouse or enter for all] & "*"

The variable [Warehouse or enter for all] is then assigned to a created
field of its own, [WAREHOUSE_SELECTED], to show on the report.

It appears that searching using a wildcard ignores null values, but we need
to be able to pick up the nulls if the user wants all records returned.

Any ideas on how to fix this?

Regards

Tom Millington

 

[John Spencer (MVP)]

It shouldn't have UNLESS the fields contained zero-length strings vice null.
Nulls wouldn't match with a wildcard search, but zero-length strings (or a
series of spaces) would.

Thanks Ken

That worked a treat.

Has something changed from A97 to A2000? I'm sure the string I was using
worked on A97.

Tom

Try this criteria expression:

Like [Warehouse or enter for all] & "*" OR [Warehouse or enter for all] Is
Null

--
Ken Snell
<MS ACCESS MVP>

Hi

We have a problem with a query.

An Access database links via ODBC to a UNIX server. To speed things, we use
the ODBC to load the relevant tables to the local PC that runs Access so
that all querying is done locally.

One of the reports we run allows the user to list all invoices within a
period. They are also allowed to select a customer code and a product

set

on
which to base the report. The product set comprises a Warehouse code (a
system grouping classification rather than a physical building) and a
product code. There will always be something in the field [PRODUCT_CODE],
but it is possible for the [WAREHOUSE] field to be null.

We used to run the database in Access 97, and I am pretty sure the following
criteria expression worked, in that if the user hit 'enter' when prompted
for the Warehouse, all records would be returned, including those with
'null' in the [WAREHOUSE] field. About six months ago we converted this
rather large database (c200 queries and reports) to Access 2000. Today, my
users tell me this report is ignoring records that have Warehouse value.

The criteria string used on the [WAREHOUSE] field is as follows

Like [Warehouse or enter for all] & "*"

The variable [Warehouse or enter for all] is then assigned to a created
field of its own, [WAREHOUSE_SELECTED], to show on the report.

It appears that searching using a wildcard ignores null values, but we need
to be able to pick up the nulls if the user wants all records returned.

Any ideas on how to fix this?

Regards

Tom Millington

 

[AAVF IT]

Thanks for the info.

I am now going through the 400+ queries in this database set to see how many
I have to modify. It looks like about 50.

One thing I have noticed. When I add the extra 'OR' statement to the
criteria and save/exit the query, when I go back into it in design view,
Access has changed the layout so that instead of having one criteria line
with the statement I entered, I now have two: one is the original, with the
'OR' statement on a second ('or') criteria line. I understand the sense in
this. However, some of these queries allow up to six variables to be
selected in the same way, all of which could come back with null-value
fields in the result. This means that when I go back into the query in
design mode, it looks like the query from hell, in that it can have a dozen
or more criteria lines. This means it is going to be a tad difficult to
maintain. Is there any way of getting Access to keep the display as I
entered it, ie one criteria line with statements containing an 'OR'
function?

Regards

Tom

It shouldn't have UNLESS the fields contained zero-length strings vice null.
Nulls wouldn't match with a wildcard search, but zero-length strings (or a
series of spaces) would.

Thanks Ken

That worked a treat.

Has something changed from A97 to A2000? I'm sure the string I was using
worked on A97.

Tom

Try this criteria expression:

Like [Warehouse or enter for all] & "*" OR [Warehouse or enter for all] Is
Null

--
Ken Snell
<MS ACCESS MVP>

Hi

We have a problem with a query.

An Access database links via ODBC to a UNIX server. To speed things, we
use
the ODBC to load the relevant tables to the local PC that runs Access so
that all querying is done locally.

One of the reports we run allows the user to list all invoices within a
period. They are also allowed to select a customer code and a

product
set

on
which to base the report. The product set comprises a Warehouse code (a
system grouping classification rather than a physical building) and a
product code. There will always be something in the field [PRODUCT_CODE],
but it is possible for the [WAREHOUSE] field to be null.

We used to run the database in Access 97, and I am pretty sure the
following
criteria expression worked, in that if the user hit 'enter' when prompted
for the Warehouse, all records would be returned, including those with
'null' in the [WAREHOUSE] field. About six months ago we converted this
rather large database (c200 queries and reports) to Access 2000.

Today,
my

users tell me this report is ignoring records that have Warehouse value.

The criteria string used on the [WAREHOUSE] field is as follows

Like [Warehouse or enter for all] & "*"

The variable [Warehouse or enter for all] is then assigned to a created
field of its own, [WAREHOUSE_SELECTED], to show on the report.

It appears that searching using a wildcard ignores null values, but we
need
to be able to pick up the nulls if the user wants all records returned.

Any ideas on how to fix this?

Regards

Tom Millington

 

[Ken Snell]

The design view changes that you see are the normal behavior for ACCESS when
you use the Criteria expression that we provided. If you open the query in
SQL, you'll see a similar "jumble".

There is no easy way to maintain this if you want to add more fields with a
similar criterion expression. I usually resort to deleting those fields with
the criteria and recreating them with the "simple" expression.

The alternative is to not use a saved query, and instead build the SQL in
code and then run it.
--
Ken Snell
<MS ACCESS MVP>

Thanks for the info.

I am now going through the 400+ queries in this database set to see how many
I have to modify. It looks like about 50.

One thing I have noticed. When I add the extra 'OR' statement to the
criteria and save/exit the query, when I go back into it in design view,
Access has changed the layout so that instead of having one criteria line
with the statement I entered, I now have two: one is the original, with the
'OR' statement on a second ('or') criteria line. I understand the sense in
this. However, some of these queries allow up to six variables to be
selected in the same way, all of which could come back with null-value
fields in the result. This means that when I go back into the query in
design mode, it looks like the query from hell, in that it can have a dozen
or more criteria lines. This means it is going to be a tad difficult to
maintain. Is there any way of getting Access to keep the display as I
entered it, ie one criteria line with statements containing an 'OR'
function?

Regards

Tom

It shouldn't have UNLESS the fields contained zero-length strings vice null.
Nulls wouldn't match with a wildcard search, but zero-length strings (or a
series of spaces) would.

Thanks Ken

That worked a treat.

Has something changed from A97 to A2000? I'm sure the string I was using
worked on A97.

Tom

Try this criteria expression:

Like [Warehouse or enter for all] & "*" OR [Warehouse or enter for all] Is
Null

--
Ken Snell
<MS ACCESS MVP>

Hi

We have a problem with a query.

An Access database links via ODBC to a UNIX server. To speed

things,

we

code

(a

and

a

product code. There will always be something in the field
[PRODUCT_CODE],
but it is possible for the [WAREHOUSE] field to be null.

We used to run the database in Access 97, and I am pretty sure the
following
criteria expression worked, in that if the user hit 'enter' when
prompted
for the Warehouse, all records would be returned, including those with
'null' in the [WAREHOUSE] field. About six months ago we converted this
rather large database (c200 queries and reports) to Access 2000. Today,
my
users tell me this report is ignoring records that have Warehouse value.

The criteria string used on the [WAREHOUSE] field is as follows

Like [Warehouse or enter for all] & "*"

The variable [Warehouse or enter for all] is then assigned to a created
field of its own, [WAREHOUSE_SELECTED], to show on the report.

It appears that searching using a wildcard ignores null values,

but

we