473,685 Members | 3,142 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Criteria testing fields that may contain null values

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_SELEC TED], 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
Nov 12 '05 #1
6 5265
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*********@aa vf.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_SELEC TED], 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

Nov 12 '05 #2
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.real net> wrote in message
news:eH******** ******@TK2MSFTN GP11.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*********@aa vf.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_SELEC TED], 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


Nov 12 '05 #4
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.real net> wrote in message
news:eH******** ******@TK2MSFTN GP11.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*********@aa vf.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_SELEC TED], 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


Nov 12 '05 #5
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.ed u> 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.real net> wrote in message
news:eH******** ******@TK2MSFTN GP11.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*********@aa vf.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_SELEC TED], 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
>
>

Nov 12 '05 #6
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.ed u> 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.real net> wrote in message
news:eH******** ******@TK2MSFTN GP11.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*********@aa vf.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_SELEC TED], 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
> >
> >
>
>


Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
3249
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 simultaneous users) some instances start erroring out. I see two different errors. One is a "Object reference not set to an instance of an object." error, which appears to always contain the same information, and the other is a "There is no row at position X.", where X is a number. Is this...
2
2918
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 vice versa. I have a form, , where I select a value for from a combo box. In my query I set the criteria for to ... My query finds the proper values for . Now I also want to find the values if I select a value for in a separate combo box. In both controls, OnChange, I set the value of
2
1716
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 querydef. When I open the form, the fields are populated just fine. However, when I add a new record using the new record control at the bottom of the subform, it gets added with NULL for the two values that were in the original WHERE clause. For example, if my query was "SELECT batch, style,...
1
1408
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 text/memo field. This field is not mandetory to be filled in, so in many cases the field contains a null value. I would like to create a query that lists all the records that do not contain the word "employed" anywhere within the description field. I tried putting NOT LIKE "employed" in...
2
5731
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 data for the selected date, for all dates upto and icluding the selected date or all records on or after the selected date The user selects either "=", >=" or "<=" from a combo box and then a date from another combobox. The combination of thse two choices is then set in an unbound textbox so...
8
6405
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 unbound text boxes on the form that also contains the combo box. The fields I am working with are date fields that are formated as Short Date. I have written an IIF statement and placed it within the Criteria section of the Date field that I am trying to filter by. The current IIF statement works...
18
13383
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 criteria I added the following expression - !! and this works like a charm. However one of my users wanted to see all of the values before choosing one of the three selections. I added the choice "All" to my combo dropdown list and tried the following iif statement as the criteria:...
3
7274
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" button is pressed, it runs a macro which runs the simple query. I have added a second field to the form, in addition to the text search field. This new field allows the user to type in an identification number, which returns all of the memo fields associated with that particular client. If the...
8
2776
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 is supposed to pull dates older than 180 days ago. The problem is that when I use that criteria for all four fields I am not getting the expected results. I am trying to find out from this query is the date in date field one is older than 180, same thing for the other three date fields. For...
0
8569
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8504
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8772
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7584
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5789
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4299
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2935
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2196
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1921
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.