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
"John Spencer (MVP)" <sp***********@SPAMNOT.umbc.edu> wrote in message
news:3F***************@SPAMNOT.umbc.edu...
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.
AAVF IT wrote:
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
"Ken Snell" <kt***********@notcomcast.realnet> wrote in message
news:eH**************@TK2MSFTNGP11.phx.gbl... Try this criteria expression:
Like [Warehouse or enter for all] & "*" OR [Warehouse or enter for
all] Is Null
--
Ken Snell
<MS ACCESS MVP>
"AAVF" <tm*********@aavf.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
> 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
>
>
