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 6 5118
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
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
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
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 > >
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>
"AAVF IT" <it***********@aavf.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk... 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 > > > > > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Shabam |
last post by:
A web application of mine developed using C# + MS SQL runs fine normally.
However when I stress test it with a load testing software (using about 60...
|
by: neptune |
last post by:
I have a query where each customer has an or . Sometimes
both fields for a customer are populated, but if is null, then
will be populated and...
|
by: JM |
last post by:
Hello,
I've created a Querydef in a Form_Load() sub. The form is a subform
that no longer has linked child fields. The form is bound to this...
|
by: LFM |
last post by:
I have a table called "employee profile" which contains a bunch of user
information. In that table I have a field called "Description" which is a...
|
by: Mark Roughton |
last post by:
I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related...
|
by: Ragbrai |
last post by:
Howdy All,
I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then...
|
by: WU10 |
last post by:
Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's...
|
by: seagullino |
last post by:
Hello,
I've developed my first Form, a simple affair that enables the user to search text in the memo fields in our database. When the "search"...
|
by: Dr Al |
last post by:
I have a table with four date fields, some of which may not be filled
in based on our data entry needs. I have a criteria set as <date()-180
which...
|
by: jalbright99669 |
last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was...
|
by: Matthew3360 |
last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
|
by: AndyPSV |
last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
|
by: Arjunsri |
last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
|
by: Matthew3360 |
last post by:
Hi,
I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
|
by: Carina712 |
last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
|
by: Rahul1995seven |
last post by:
Introduction:
In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...
| |