Getting a query to accept a null value

T

TESA0_4

Hi,
I have created a form with two unbound combo boxes. One allows the user to
select a code that represents a type of risk and the other allows the user to
select a code that represents a location. I am building a query that
includes the unbound combo boxes as selection criteria.
The user must select a risk type but need not necessarily select a location
code. If they select only a risk type and no location code I want the query
to return all records with the selected risk type and a null location value.
(Risk type is always populated whereas location is optional). If the use
selects a risk type and location code then I want the query to return records
where the risk type and location value match the user's selections.
I find that when I insert [Forms]![frmRegisterSearch]![comboLocation] in the
query criteria all works well if a non-null value is selected in the location
combo control, but if the combo control is left blank, the query does not
return any values.
I have tried inserting code like:
IIf(IsNull([Forms]![frmRegisterSearch]![comboLocation]),Null,[Forms]![frmRegisterSearch]![comboLocation]) but this does not work.
The SQL View of my current code is:
SELECT tblRegister.RegID, tblRegister.Type, tblRegister.Locn,
tblRegister.Manufacturer, tblRegister.Model, tblRegister.Archived
FROM tblRegister
WHERE (((tblRegister.Type)=[Forms]![frmRegisterSearch]![comboRiskType1]) AND
((tblRegister.Locn)=IIf(IsNull([Forms]![frmRegisterSearch]![comboLocation]),Null,[Forms]![frmRegisterSearch]![comboLocation])));
I realise my question is very similar to other questions on this site but I
just don't seem to be able to grasp how the other replies apply to my
particular situation.
Thanks for any help that can be offered.
 
J

John Spencer

One solution:

SELECT tblRegister.RegID, tblRegister.Type, tblRegister.Locn,
tblRegister.Manufacturer, tblRegister.Model, tblRegister.Archived
FROM tblRegister
WHERE tblRegister.Type=[Forms]![frmRegisterSearch]![comboRiskType1]
AND (tblRegister.Locn=[Forms]![frmRegisterSearch]![comboLocation] OR
[Forms]![frmRegisterSearch]![comboLocation] is Null)

Alternative solution:

SELECT tblRegister.RegID, tblRegister.Type, tblRegister.Locn,
tblRegister.Manufacturer, tblRegister.Model, tblRegister.Archived
FROM tblRegister
WHERE tblRegister.Type=[Forms]![frmRegisterSearch]![comboRiskType1]
AND IIF([Forms]![frmRegisterSearch]![comboLocation] Is Null, TRUE,
tblRegister.Locn=[Forms]![frmRegisterSearch]![comboLocation])


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
T

TESA0_4

Hi John,

Thanks for your assistance. I tried both your options. The first worked but
the second failed with a message saying the code was too complex.

Terry

John Spencer said:
One solution:

SELECT tblRegister.RegID, tblRegister.Type, tblRegister.Locn,
tblRegister.Manufacturer, tblRegister.Model, tblRegister.Archived
FROM tblRegister
WHERE tblRegister.Type=[Forms]![frmRegisterSearch]![comboRiskType1]
AND (tblRegister.Locn=[Forms]![frmRegisterSearch]![comboLocation] OR
[Forms]![frmRegisterSearch]![comboLocation] is Null)

Alternative solution:

SELECT tblRegister.RegID, tblRegister.Type, tblRegister.Locn,
tblRegister.Manufacturer, tblRegister.Model, tblRegister.Archived
FROM tblRegister
WHERE tblRegister.Type=[Forms]![frmRegisterSearch]![comboRiskType1]
AND IIF([Forms]![frmRegisterSearch]![comboLocation] Is Null, TRUE,
tblRegister.Locn=[Forms]![frmRegisterSearch]![comboLocation])


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

TESA0_4 said:
Hi,
I have created a form with two unbound combo boxes. One allows the user to
select a code that represents a type of risk and the other allows the user to
select a code that represents a location. I am building a query that
includes the unbound combo boxes as selection criteria.
The user must select a risk type but need not necessarily select a location
code. If they select only a risk type and no location code I want the query
to return all records with the selected risk type and a null location value.
(Risk type is always populated whereas location is optional). If the use
selects a risk type and location code then I want the query to return records
where the risk type and location value match the user's selections.
I find that when I insert [Forms]![frmRegisterSearch]![comboLocation] in the
query criteria all works well if a non-null value is selected in the location
combo control, but if the combo control is left blank, the query does not
return any values.
I have tried inserting code like:
IIf(IsNull([Forms]![frmRegisterSearch]![comboLocation]),Null,[Forms]![frmRegisterSearch]![comboLocation]) but this does not work.
The SQL View of my current code is:
SELECT tblRegister.RegID, tblRegister.Type, tblRegister.Locn,
tblRegister.Manufacturer, tblRegister.Model, tblRegister.Archived
FROM tblRegister
WHERE (((tblRegister.Type)=[Forms]![frmRegisterSearch]![comboRiskType1]) AND
((tblRegister.Locn)=IIf(IsNull([Forms]![frmRegisterSearch]![comboLocation]),Null,[Forms]![frmRegisterSearch]![comboLocation])));
I realise my question is very similar to other questions on this site but I
just don't seem to be able to grasp how the other replies apply to my
particular situation.
Thanks for any help that can be offered.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Values in a Query Criteria 1
12 query criteria from "Search" form... 8
Access Determine what query to run based on a checkbox 0
Getting a null value to display zero in count query 1
Insert query with null column and null value 10
Query criteria - exclude data and return all records if null 3
Query not returning null value 3
Adding a NULL value 8

Top