Select query driving me mad (using *)

  • Thread starter Thread starter TheSingingCat
  • Start date Start date
T

TheSingingCat

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!
 
Try this =
AND (((MAIN.Location) Like [Forms]![PRINT MENU]![Combo72] & "*" Or
MAIN.Location Is Null)))
ORDER BY MAIN.BOOKDATE;
 
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
KARL said:
Try this =
AND (((MAIN.Location) Like [Forms]![PRINT MENU]![Combo72] & "*" Or
MAIN.Location Is Null)))
ORDER BY MAIN.BOOKDATE;

TheSingingCat said:
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!
Click to expand...
Click to expand...
 
Thanks Karl / John, I'll try these suggestions, though I was under the
impression that * would even retrieve null records.


John Spencer (MVP) said:
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
KARL said:
Try this =
AND (((MAIN.Location) Like [Forms]![PRINT MENU]![Combo72] & "*" Or
MAIN.Location Is Null)))
ORDER BY MAIN.BOOKDATE;

TheSingingCat said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top