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

On Wed, 7 Nov 2007 14:08:00 -0800, Bonnie <(E-Mail Removed)>
wrote:

>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]

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

"John W. Vinson" wrote:

> On Wed, 7 Nov 2007 14:08:00 -0800, Bonnie <(E-Mail Removed)>
> wrote:
>
> >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]
>
>
>

On Wed, 7 Nov 2007 16:54:01 -0800, Bonnie <(E-Mail Removed)>
wrote:

>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]

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

"John W. Vinson" wrote:

> On Wed, 7 Nov 2007 16:54:01 -0800, Bonnie <(E-Mail Removed)>
> wrote:
>
> >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]
>

On Thu, 8 Nov 2007 04:05:00 -0800, Bonnie <(E-Mail Removed)>
wrote:

>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]

Yup it did. Thank you sir.

"John W. Vinson" wrote:

> On Thu, 8 Nov 2007 04:05:00 -0800, Bonnie <(E-Mail Removed)>
> wrote:
>
> >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]
>