"All" in query criteria

[Alan B. Densky]

I have numerous queries that I run with the selection criteria of Like *,
and they all work very nicely except when the criteria field is null. It
seems that when the field is null, Access doesn't like the "Like *" criteria
and it doesn't include any of these records in the record set. How can I
overcome this glitch.

By the way, the field that I'm selecting on is not an index, and a null
value (the user has not filled in any data) in this field is perfectly
normal.

Thanks in advance,
Alan B. Densky

 

[Duane Hookom]

Do you have at least a Where clause you could share with us? You state "Like
*" but I really doubt you have this in your query.

 

[Alan B. Densky]

Hi Duane,

You are correct, actually I'm referencing a field in a form, and that field
can have an * in it. Here is my select query:

SELECT Customers.[Customer Number], Customers.Company, Customers.City

FROM Customers

WHERE (((Customers.City) Like [Forms]![Form1]![City]));



If there is no city in the field, then that record is not included in the
recordset when the * is in the field. I've tried replacing the direct
reference to the field in the form by using a function in the query, and
making that function = * (which is how I usually do things), but that didn't
make any difference.

Any ideas?

Alan B. Densky

 

[Duane Hookom]

Use:
WHERE Customers.City & "" Like [Forms]![Form1]![City];
This solution expects the user to enter *s. You could add them to the
criteria like:
WHERE Customers.City & "" Like "*" & [Forms]![Form1]![City] & "*";

--
Duane Hookom
MS Access MVP

Hi Duane,

You are correct, actually I'm referencing a field in a form, and that
field
can have an * in it. Here is my select query:

SELECT Customers.[Customer Number], Customers.Company, Customers.City

FROM Customers

WHERE (((Customers.City) Like [Forms]![Form1]![City]));



If there is no city in the field, then that record is not included in the
recordset when the * is in the field. I've tried replacing the direct
reference to the field in the form by using a function in the query, and
making that function = * (which is how I usually do things), but that
didn't
make any difference.

Any ideas?

Alan B. Densky

 

[Alan B. Densky]

Hi Duane,
Thanks a lot. That did the trick!

Alan B. Densky

Use:
WHERE Customers.City & "" Like [Forms]![Form1]![City];
This solution expects the user to enter *s. You could add them to the
criteria like:
WHERE Customers.City & "" Like "*" & [Forms]![Form1]![City] & "*";

--
Duane Hookom
MS Access MVP

Hi Duane,

You are correct, actually I'm referencing a field in a form, and that
field
can have an * in it. Here is my select query:

SELECT Customers.[Customer Number], Customers.Company, Customers.City

FROM Customers

WHERE (((Customers.City) Like [Forms]![Form1]![City]));



If there is no city in the field, then that record is not included in the
recordset when the * is in the field. I've tried replacing the direct
reference to the field in the form by using a function in the query, and
making that function = * (which is how I usually do things), but that
didn't
make any difference.

Any ideas?

Alan B. Densky

Do you have at least a Where clause you could share with us? You state "Like
*" but I really doubt you have this in your query.

--
Duane Hookom
MS Access MVP


I have numerous queries that I run with the selection criteria of Like
*,
and they all work very nicely except when the criteria field is null. It
seems that when the field is null, Access doesn't like the "Like *"
criteria
and it doesn't include any of these records in the record set. How

can
I

overcome this glitch.

By the way, the field that I'm selecting on is not an index, and a null
value (the user has not filled in any data) in this field is perfectly
normal.

Thanks in advance,
Alan B. Densky