Select query driving me mad (using *)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TheSingingCat - 26 Sep 2005 21:35 GMT

Hi gang,

I've got a little issue here that is proving to be quite frustrating..
hopefully someone could show me my error.

I have a query that is has the following fields
'category','address','location' all from the same table (and this query is
based only off the 1 table)

In they query criteria I have the following for each field.

Category = "Sprockets"
address =
Location = Like IIf(IsNull([Forms]![PRINT
MENU]![Combo72]),"*",[Forms]![PRINT MENU]![Combo72])

When I run the query I get no results... so I remove the entire line so
'location' is just blank in the query critera. Run again and suddenly I get
pile of records with all my sprockets.

Odd I think... so in the location criteria I just put in a * and run query.
No results. Change it to 'is null' and run, I get my records displayed.

Now as it is, there is currently no data for the 'location' field in the
table (null), but that should not impact the query when searching.

What is wrong with this that when I use a * I get no data back? I am missing
something..

My actual SQL (that returns no records)

SELECT DISTINCTROW MAIN.[ORDER#], MAIN.CUSTOMER, MAIN.SALESMAN,
MAIN.CATEGORY, MAIN.STATUS, MAIN.ADDRESS, MAIN.BOOKDATE, MAIN.DEL_TIME,
MAIN.DEL_STATUS, MAIN.PRICE, MAIN.Location
FROM MAIN
WHERE (((MAIN.CATEGORY)="WALLS") AND ((MAIN.BOOKDATE) Between
DateSerial(Year(Now()),Month(Now())+0,1) And
DateSerial(Year(Date()),Month(Date())+3,0)) AND
((MAIN.DEL_STATUS)<>"shipped") AND ((MAIN.Location) Like "*"))
ORDER BY MAIN.BOOKDATE;

and if I just remove ((MAIN.Location) Like "*")) it returns data....

Access 97' the location field is text but a combo box value list in table
design.

Thanks!

Reply to this Message

KARL DEWEY - 26 Sep 2005 21:55 GMT

Try this =
AND (((MAIN.Location) Like [Forms]![PRINT MENU]![Combo72] & "*" Or
MAIN.Location Is Null)))
ORDER BY MAIN.BOOKDATE;

> Hi gang,
>
[quoted text clipped - 43 lines]
>
> Thanks!

Reply to this Message

John Spencer (MVP) - 26 Sep 2005 23:33 GMT

And as to why yours doesn't work.

Your fields are NULL, they aren't an empty string. NULL is NEVER equal to
anything, so you must use the IS Null operator to find records where the field
is null.

You might want to change Karl Dewey's suggestion to

SELECT DISTINCTROW MAIN.[ORDER#], MAIN.CUSTOMER, MAIN.SALESMAN,
MAIN.CATEGORY, MAIN.STATUS, MAIN.ADDRESS, MAIN.BOOKDATE, MAIN.DEL_TIME,
MAIN.DEL_STATUS, MAIN.PRICE, MAIN.Location
FROM MAIN
WHERE MAIN.CATEGORY)="WALLS"
AND MAIN.BOOKDATE Between DateSerial(Year(Now()),Month(Now())+0,1) And
DateSerial(Year(Date()),Month(Date())+3,0)
AND MAIN.DEL_STATUS<>"shipped"
AND (MAIN.Location = [Forms]![PRINT MENU]![Combo72] Or [Forms]![PRINT
MENU]![Combo72] Is Null)
ORDER BY MAIN.BOOKDATE;

IF you do this in the query grid then enter
[Forms]![PRINT MENU]![Combo72] Or [Forms]![PRINT MENU]![Combo72] Is Null
in the criteria cell under Location

When you save the above Access will reorganize it, but it should still work for you

> Try this =
> AND (((MAIN.Location) Like [Forms]![PRINT MENU]![Combo72] & "*" Or
[quoted text clipped - 48 lines]
> >
> > Thanks!

Reply to this Message

TheSingingCat - 27 Sep 2005 17:46 GMT

Thanks Karl / John, I'll try these suggestions, though I was under the
impression that * would even retrieve null records.

> And as to why yours doesn't work.
>
[quoted text clipped - 83 lines]
>> >
>> > Thanks!

Reply to this Message

TheSingingCat - 27 Sep 2005 18:01 GMT

That worked like a charm, you guys rock - thanks again!

tsc

> Thanks Karl / John, I'll try these suggestions, though I was under the
> impression that * would even retrieve null records.
>
>> And as to why yours doesn't work.

Reply to this Message