Passing a null value to a query

[PJFry]

I am designing a form that allows a user to specify query conditions. I run
into trouble is the user does not want to limit on a particular item.

I have a form, fDrill that has three combo boxes, once of which is for the
customer name, cboCustName. The query that uses these conditions is qDrill.
The Customer Name field in qDrill has [Forms]![fDrill]![cboCustName] as the
condition. If the user does not choose a customer, then the query returns
nothing. How do I set either the form or the query to recognize that a combo
box with no value means that they do not want to add a condition to that
field and return all the customers?

Thanks!
PJ

 

[Ken Snell \(MVP\)]

Something like this:

SELECT *
FROM Tablename
WHERE FieldName = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL;

For three conditions:

SELECT *
FROM Tablename
WHERE (FieldName1 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL) AND
(FieldName2 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL)
(FieldName3 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL);

 

[Ken Snell \(MVP\)]

Sorry - typo in the "three condition" example -- forgot AND between second
and third conditions:

SELECT *
FROM Tablename
WHERE (FieldName1 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL) AND
(FieldName2 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL) AND
(FieldName3 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL);

--

Ken Snell
<MS ACCESS MVP>

Something like this:

SELECT *
FROM Tablename
WHERE FieldName = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL;

For three conditions:

SELECT *
FROM Tablename
WHERE (FieldName1 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL) AND
(FieldName2 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL)
(FieldName3 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL);

--

Ken Snell
<MS ACCESS MVP>

I am designing a form that allows a user to specify query conditions. I
run
into trouble is the user does not want to limit on a particular item.

I have a form, fDrill that has three combo boxes, once of which is for
the
customer name, cboCustName. The query that uses these conditions is
qDrill.
The Customer Name field in qDrill has [Forms]![fDrill]![cboCustName] as
the
condition. If the user does not choose a customer, then the query
returns
nothing. How do I set either the form or the query to recognize that a
combo
box with no value means that they do not want to add a condition to that
field and return all the customers?

Thanks!
PJ

 

[PJFry]

That did it. It took me a while to nail it down because my form references
were not in order, I had a form reference followed by a few static query
conditions followed by the other form references and the SQL didn't like
that. Once I ordered the references and conditions, it worked great. Wicked
looking in design view. Glad I didn't try to figure it out there.

Thanks Ken!

PJ

Sorry - typo in the "three condition" example -- forgot AND between second
and third conditions:

SELECT *
FROM Tablename
WHERE (FieldName1 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL) AND
(FieldName2 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL) AND
(FieldName3 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL);

--

Ken Snell
<MS ACCESS MVP>

Something like this:

SELECT *
FROM Tablename
WHERE FieldName = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL;

For three conditions:

SELECT *
FROM Tablename
WHERE (FieldName1 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL) AND
(FieldName2 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL)
(FieldName3 = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName IS NULL);

--

Ken Snell
<MS ACCESS MVP>

I am designing a form that allows a user to specify query conditions. I
run
into trouble is the user does not want to limit on a particular item.

I have a form, fDrill that has three combo boxes, once of which is for
the
customer name, cboCustName. The query that uses these conditions is
qDrill.
The Customer Name field in qDrill has [Forms]![fDrill]![cboCustName] as
the
condition. If the user does not choose a customer, then the query
returns
nothing. How do I set either the form or the query to recognize that a
combo
box with no value means that they do not want to add a condition to that
field and return all the customers?

Thanks!
PJ