Null Values

[Guest]

Hello there! I am using Access XP and have a question about using criteria to
retrieve records. In the query, there are roughly 50 fields associated with a
table. Right now I am trying to create a query that will retrieve records
based upon 2 fields called AcctNumber and AcctType. In the query fields I
have the following code:

Field:AcctType
Table:Master
Sortblank)
Showchecked)
Criteria:[Enter AcctType:]
or: Is Null

Field:AcctNumber
Table:Master
Sortblank)
Showchecked)
Criteria:Between [Enter Minimum 3 Digit Account (00X Format):] And [Enter
Maximum 3 Digit Account (XXX Format):]
or:Is Null

Basically, I would like the program to retrieve reccords based upon either
AcctNumber, AcctType, Both AcctNumber & AcctType, or retrieve all records if
the prompt boxes are left blank. I don't know if I've overlooked something
but it seems if I do not fill in both fields (AcctNumber or AcctType) or only
fill in one field or leave the fields blank, my query retrieves nothing...any
suggestions? I've read the other posts about writing a SQL statement and
that's not a problem except I don't know how I would go about doing it. Any
help would be apprecciated!

 

[Allen Browne]

The way you specified your criteria, the query returns the values where the
field is null (possibly no records) or matches the parameter. So if you
leave the parameter blank, only the records where the field is null get
returned.

Switch the query to SQL View.
Change the WHERE clause to something like this:

WHERE (([Enter AcctType:] Is Null) OR ([AcctType] = [Enter AcctType:]))
AND ...

The 2nd item is complicated by the fact that there are 4 possible cases for
the 2 paramters:
a) Both supplied you want records between the values.
b) Both blank: you want all records.
c) First supplied, and last blank: you want all records from the starting
number onwards?
d) Last one supplied, and first blank: you want all records up to the ending
number?

While it would be possible to do that with nested IIf() statements, it's
pretty messy. A better alternative might be to build the WHERE clause
dynamically, from just the supplied values. For example, if the query is the
source for a report, you could leave the criteria out of the query and use
the WhereCondition of OpenReport instead. Of it is is used to filter a form,
you could leave the criteria out of the query, and create the string to use
as the Filter of the form.

For an example of how to do that, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

 

[Guest]

Thanks Allen, leave it to me to take a seemingly simple idea and turn it into
a major task. I appreciate your help. I guess my ultimate idea of making
every field in the query a possible parameter would be quite daunting. Thanks
again, I'll give your idea a shot...

The way you specified your criteria, the query returns the values where the
field is null (possibly no records) or matches the parameter. So if you
leave the parameter blank, only the records where the field is null get
returned.

Switch the query to SQL View.
Change the WHERE clause to something like this:

WHERE (([Enter AcctType:] Is Null) OR ([AcctType] = [Enter AcctType:]))
AND ...

The 2nd item is complicated by the fact that there are 4 possible cases for
the 2 paramters:
a) Both supplied you want records between the values.
b) Both blank: you want all records.
c) First supplied, and last blank: you want all records from the starting
number onwards?
d) Last one supplied, and first blank: you want all records up to the ending
number?

While it would be possible to do that with nested IIf() statements, it's
pretty messy. A better alternative might be to build the WHERE clause
dynamically, from just the supplied values. For example, if the query is the
source for a report, you could leave the criteria out of the query and use
the WhereCondition of OpenReport instead. Of it is is used to filter a form,
you could leave the criteria out of the query, and create the string to use
as the Filter of the form.

For an example of how to do that, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello there! I am using Access XP and have a question about using criteria
to
retrieve records. In the query, there are roughly 50 fields associated
with a
table. Right now I am trying to create a query that will retrieve records
based upon 2 fields called AcctNumber and AcctType. In the query fields I
have the following code:

Field:AcctType
Table:Master
Sortblank)
Showchecked)
Criteria:[Enter AcctType:]
or: Is Null

Field:AcctNumber
Table:Master
Sortblank)
Showchecked)
Criteria:Between [Enter Minimum 3 Digit Account (00X Format):] And [Enter
Maximum 3 Digit Account (XXX Format):]
or:Is Null

Basically, I would like the program to retrieve reccords based upon either
AcctNumber, AcctType, Both AcctNumber & AcctType, or retrieve all records
if
the prompt boxes are left blank. I don't know if I've overlooked something
but it seems if I do not fill in both fields (AcctNumber or AcctType) or
only
fill in one field or leave the fields blank, my query retrieves
nothing...any
suggestions? I've read the other posts about writing a SQL statement and
that's not a problem except I don't know how I would go about doing it.
Any
help would be apprecciated!

 

[Guest]

But wait! In SQL view I changed the AND operator to an OR operator and now my
query is working as I intended...two questions though...1)Is there a
universal default symbol to use with a criteria prompt box that means "all
values"? 2) what is the maximum amount of OR statements for Access? Thanks!

Thanks Allen, leave it to me to take a seemingly simple idea and turn it into
a major task. I appreciate your help. I guess my ultimate idea of making
every field in the query a possible parameter would be quite daunting. Thanks
again, I'll give your idea a shot...

The way you specified your criteria, the query returns the values where the
field is null (possibly no records) or matches the parameter. So if you
leave the parameter blank, only the records where the field is null get
returned.

Switch the query to SQL View.
Change the WHERE clause to something like this:

WHERE (([Enter AcctType:] Is Null) OR ([AcctType] = [Enter AcctType:]))
AND ...

The 2nd item is complicated by the fact that there are 4 possible cases for
the 2 paramters:
a) Both supplied you want records between the values.
b) Both blank: you want all records.
c) First supplied, and last blank: you want all records from the starting
number onwards?
d) Last one supplied, and first blank: you want all records up to the ending
number?

While it would be possible to do that with nested IIf() statements, it's
pretty messy. A better alternative might be to build the WHERE clause
dynamically, from just the supplied values. For example, if the query is the
source for a report, you could leave the criteria out of the query and use
the WhereCondition of OpenReport instead. Of it is is used to filter a form,
you could leave the criteria out of the query, and create the string to use
as the Filter of the form.

For an example of how to do that, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello there! I am using Access XP and have a question about using criteria
to
retrieve records. In the query, there are roughly 50 fields associated
with a
table. Right now I am trying to create a query that will retrieve records
based upon 2 fields called AcctNumber and AcctType. In the query fields I
have the following code:

Field:AcctType
Table:Master
Sortblank)
Showchecked)
Criteria:[Enter AcctType:]
or: Is Null

Field:AcctNumber
Table:Master
Sortblank)
Showchecked)
Criteria:Between [Enter Minimum 3 Digit Account (00X Format):] And [Enter
Maximum 3 Digit Account (XXX Format):]
or:Is Null

Basically, I would like the program to retrieve reccords based upon either
AcctNumber, AcctType, Both AcctNumber & AcctType, or retrieve all records
if
the prompt boxes are left blank. I don't know if I've overlooked something
but it seems if I do not fill in both fields (AcctNumber or AcctType) or
only
fill in one field or leave the fields blank, my query retrieves
nothing...any
suggestions? I've read the other posts about writing a SQL statement and
that's not a problem except I don't know how I would go about doing it.
Any
help would be apprecciated!

 

[Allen Browne]

1. People try
Like "*"
to return all values from a Text field. However, this does not return rows
where there is no value, is not a good idea for fields of other types
(Number, Date, ...), and is not an efficient solution.

2. Up to 99 ANDs in a WHERE clause.

If you are trying to create a search form where you can select from dozens
of fields, you really need to build the WHERE clause dynamically, using only
the fields where the user entered something.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

But wait! In SQL view I changed the AND operator to an OR operator and now
my
query is working as I intended...two questions though...1)Is there a
universal default symbol to use with a criteria prompt box that means "all
values"? 2) what is the maximum amount of OR statements for Access?
Thanks!

Thanks Allen, leave it to me to take a seemingly simple idea and turn it
into
a major task. I appreciate your help. I guess my ultimate idea of making
every field in the query a possible parameter would be quite daunting.
Thanks
again, I'll give your idea a shot...

The way you specified your criteria, the query returns the values where
the
field is null (possibly no records) or matches the parameter. So if you
leave the parameter blank, only the records where the field is null get
returned.

Switch the query to SQL View.
Change the WHERE clause to something like this:

WHERE (([Enter AcctType:] Is Null) OR ([AcctType] = [Enter AcctType:]))
AND ...

The 2nd item is complicated by the fact that there are 4 possible cases
for
the 2 paramters:
a) Both supplied you want records between the values.
b) Both blank: you want all records.
c) First supplied, and last blank: you want all records from the
starting
number onwards?
d) Last one supplied, and first blank: you want all records up to the
ending
number?

While it would be possible to do that with nested IIf() statements,
it's
pretty messy. A better alternative might be to build the WHERE clause
dynamically, from just the supplied values. For example, if the query
is the
source for a report, you could leave the criteria out of the query and
use
the WhereCondition of OpenReport instead. Of it is is used to filter a
form,
you could leave the criteria out of the query, and create the string to
use
as the Filter of the form.

For an example of how to do that, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello there! I am using Access XP and have a question about using
criteria
to
retrieve records. In the query, there are roughly 50 fields
associated
with a
table. Right now I am trying to create a query that will retrieve
records
based upon 2 fields called AcctNumber and AcctType. In the query
fields I
have the following code:

Field:AcctType
Table:Master
Sortblank)
Showchecked)
Criteria:[Enter AcctType:]
or: Is Null

Field:AcctNumber
Table:Master
Sortblank)
Showchecked)
Criteria:Between [Enter Minimum 3 Digit Account (00X Format):] And
[Enter
Maximum 3 Digit Account (XXX Format):]
or:Is Null

Basically, I would like the program to retrieve reccords based upon
either
AcctNumber, AcctType, Both AcctNumber & AcctType, or retrieve all
records
if
the prompt boxes are left blank. I don't know if I've overlooked
something
but it seems if I do not fill in both fields (AcctNumber or AcctType)
or
only
fill in one field or leave the fields blank, my query retrieves
nothing...any
suggestions? I've read the other posts about writing a SQL statement
and
that's not a problem except I don't know how I would go about doing
it.
Any
help would be apprecciated!