criteria with null value

[Guest]

Hi,

I am running this SQL to find the price of an item:

SELECT PARTS.UNITPRICE, PARTS.ITEMDESC, PARTS.WIDTH, PARTS.LENGTH,
PARTS.LHEIGHT
FROM PARTS
WHERE (((PARTS.ITEMDESC)=forms!form1.descrip) And
((PARTS.WIDTH)=forms!form1.text2) And ((PARTS.LENGTH)=forms!form1.text4) And
((PARTS.LHEIGHT)=forms!form1.text6));

The problem is, if any of these items are null - the query shows no records.
How can I conditionally check and ignore null values?

Thanks in advance,

Bonnie

 

[John W. Vinson]

The problem is, if any of these items are null - the query shows no records.
How can I conditionally check and ignore null values?

Just do so:

SELECT PARTS.UNITPRICE, PARTS.ITEMDESC, PARTS.WIDTH, PARTS.LENGTH,
PARTS.LHEIGHT
FROM PARTS
WHERE
(((PARTS.ITEMDESC)=[forms]![form1]![descrip] OR [forms]![form1]![descrip] IS
NULL)
And
((PARTS.WIDTH)=[forms]![form1]![text2] OR [forms]![form1]![text2] IS NULL)
And
((PARTS.LENGTH)=[forms]![form1]![text4] OR [forms]![form1]![text4] IS NULL)
And
((PARTS.LHEIGHT)=[forms]![form1]![text6] OR [forms]![form1]![text6] IS NULL));

Try to avoid going back to the query grid after pasting the SQL - it will add
all four forms references as calculated fields and make an absolute hash of
the display. Just save the SQL. Also note that ! is a better delimiter for
forms references, and that the square brackets are recommended.

Note also that running the query without filling in any of the textboxes will
return all records. This may be what you want...

John W. Vinson [MVP]

 

[Guest]

Hi John,

Actually I want to pull up just the records that meet the criteria and
ignore null values. What my SQL code did was pull up nothing if one of the
fields was blank - I want it to ignore a field if it is blank.

Thanks

The problem is, if any of these items are null - the query shows no records.
How can I conditionally check and ignore null values?

Just do so:

SELECT PARTS.UNITPRICE, PARTS.ITEMDESC, PARTS.WIDTH, PARTS.LENGTH,
PARTS.LHEIGHT
FROM PARTS
WHERE
(((PARTS.ITEMDESC)=[forms]![form1]![descrip] OR [forms]![form1]![descrip] IS
NULL)
And
((PARTS.WIDTH)=[forms]![form1]![text2] OR [forms]![form1]![text2] IS NULL)
And
((PARTS.LENGTH)=[forms]![form1]![text4] OR [forms]![form1]![text4] IS NULL)
And
((PARTS.LHEIGHT)=[forms]![form1]![text6] OR [forms]![form1]![text6] IS NULL));

Try to avoid going back to the query grid after pasting the SQL - it will add
all four forms references as calculated fields and make an absolute hash of
the display. Just save the SQL. Also note that ! is a better delimiter for
forms references, and that the square brackets are recommended.

Note also that running the query without filling in any of the textboxes will
return all records. This may be what you want...

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,

Actually I want to pull up just the records that meet the criteria and
ignore null values. What my SQL code did was pull up nothing if one of the
fields was blank - I want it to ignore a field if it is blank.

Eh?

So you want a criterion of 123 to pull up all records where the field contains
123, and also all records where the field is NULL? That seems VERY odd. To do
so, though, just add

OR IS NULL

after the form reference:

((PARTS.WIDTH)=[forms]![form1]![text2] OR IS NULL)

Or do you want to ignore the *criterion* if the criterion is blank? My query
will do exactly that.

John W. Vinson [MVP]

 

[Guest]

Sorry John, I'm having trouble articulating this. I want to pull up the
unitprice from a record with (width=123 or width is null) and (length=345 or
length is null) and (height=567 or height is null). Some items don't use all
of the size descriptors so those size descriptors will be null in those
records.

Does that make sense?

Thanks again.

Bonnie

Hi John,

Actually I want to pull up just the records that meet the criteria and
ignore null values. What my SQL code did was pull up nothing if one of the
fields was blank - I want it to ignore a field if it is blank.

Eh?

So you want a criterion of 123 to pull up all records where the field contains
123, and also all records where the field is NULL? That seems VERY odd. To do
so, though, just add

OR IS NULL

after the form reference:

((PARTS.WIDTH)=[forms]![form1]![text2] OR IS NULL)

Or do you want to ignore the *criterion* if the criterion is blank? My query
will do exactly that.

John W. Vinson [MVP]

 

[John W. Vinson]

Sorry John, I'm having trouble articulating this. I want to pull up the
unitprice from a record with (width=123 or width is null) and (length=345 or
length is null) and (height=567 or height is null). Some items don't use all
of the size descriptors so those size descriptors will be null in those
records.

Does that make sense?

Ok, the OR IS NULL criterion on each field should work then.

John W. Vinson [MVP]

 

[Guest]

Yup it did. Thank you sir.

Ok, the OR IS NULL criterion on each field should work then.

John W. Vinson [MVP]