Like NZ Doesnt Work Need Help!!!

[Guest]

Hi,

i created the following query:

SELECT [outlet data].[Out Nbr], [outlet data].[Outlet Name], [outlet
data].[Outlet Address], [outlet data].[Out City Name], [outlet data].[Out
State Code], [outlet data].[Out Zip Code]
FROM [outlet data]
WHERE ((([outlet data].[Outlet Name]) Like
nz([forms]![filter]![outname],"*")) AND (([outlet data].[Outlet Address])
Like nz([forms]![filter]![outaddress],"*")) AND (([outlet data].[Out City
Name]) Like nz([forms]![filter]![outcity],"*")) AND (([outlet data].[Out Zip
Code]) Like nz([forms]![filter]![outzip],"*")));

What i want it to do is that when i update my search fields on my "filter
form" the query will return all the records that match that criteria in that
field and return all records in a field if left blank.
When i update the fields on my forms, the query just goes blank, what am i
doing wrong, i've done the exact same thing in another database and it works
just fine. For some reason it doesnt want to work again. I also noticed that
it works fine with two of the search fields: [outzip] and [outaddress] but
not with [outname] and [outcity]. I would really appreciate any help.

Thanks

 

[Allen Browne]

Try this kind of thing:
SELECT [outlet data].[Out Nbr],
[outlet data].[Outlet Name],
[outlet data].[Outlet Address],
[outlet data].[Out City Name],
[outlet data].[Out State Code],
[outlet data].[Out Zip Code]
FROM [outlet data]
WHERE (([forms]![filter]![outname] Is Null)
OR ([outlet data].[Outlet Name] = [forms]![filter]![outname]))
AND (([forms]![filter]![outaddress] Is Null)
OR ([outlet data].[Outlet Address] = [forms]![filter]![outaddress]))
AND ...

When [forms]![filter]![outname] is in fact null, the first part of the
expression is True for all records, and so all are returned. If it is not
null, the OR part is evaluated, so only the matching records are returned.
The bracketing is important when you mix ANDs and ORs.

An alternative approach is to build the WHERE clause dynamically, from only
those controls where the user entered a search value. For an example of how
to do that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[John Spencer]

And to explain the reason your criteria may be failing is that if your
fields contain null values (not zero-length strings) then the like "*" will
not match those fields.

Try this kind of thing:
SELECT [outlet data].[Out Nbr],
[outlet data].[Outlet Name],
[outlet data].[Outlet Address],
[outlet data].[Out City Name],
[outlet data].[Out State Code],
[outlet data].[Out Zip Code]
FROM [outlet data]
WHERE (([forms]![filter]![outname] Is Null)
OR ([outlet data].[Outlet Name] = [forms]![filter]![outname]))
AND (([forms]![filter]![outaddress] Is Null)
OR ([outlet data].[Outlet Address] =
[forms]![filter]![outaddress]))
AND ...

When [forms]![filter]![outname] is in fact null, the first part of the
expression is True for all records, and so all are returned. If it is not
null, the OR part is evaluated, so only the matching records are returned.
The bracketing is important when you mix ANDs and ORs.

An alternative approach is to build the WHERE clause dynamically, from
only those controls where the user entered a search value. For an example
of how to do that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i created the following query:

SELECT [outlet data].[Out Nbr], [outlet data].[Outlet Name], [outlet
data].[Outlet Address], [outlet data].[Out City Name], [outlet data].[Out
State Code], [outlet data].[Out Zip Code]
FROM [outlet data]
WHERE ((([outlet data].[Outlet Name]) Like
nz([forms]![filter]![outname],"*")) AND (([outlet data].[Outlet Address])
Like nz([forms]![filter]![outaddress],"*")) AND (([outlet data].[Out City
Name]) Like nz([forms]![filter]![outcity],"*")) AND (([outlet data].[Out
Zip
Code]) Like nz([forms]![filter]![outzip],"*")));

What i want it to do is that when i update my search fields on my "filter
form" the query will return all the records that match that criteria in
that
field and return all records in a field if left blank.
When i update the fields on my forms, the query just goes blank, what am
i
doing wrong, i've done the exact same thing in another database and it
works
just fine. For some reason it doesnt want to work again. I also noticed
that
it works fine with two of the search fields: [outzip] and [outaddress]
but
not with [outname] and [outcity]. I would really appreciate any help.

 

[Guest]

I appreciate both of your responses but i just found out last nite that i
have a problem with the data on those two columns. The data seems to contain
spaces and when i run the query, my criteria doesnt match any fields because
the data contains spaces. I did test this query with a different set of data
and it worked fine.

thanks!

And to explain the reason your criteria may be failing is that if your
fields contain null values (not zero-length strings) then the like "*" will
not match those fields.

Try this kind of thing:
SELECT [outlet data].[Out Nbr],
[outlet data].[Outlet Name],
[outlet data].[Outlet Address],
[outlet data].[Out City Name],
[outlet data].[Out State Code],
[outlet data].[Out Zip Code]
FROM [outlet data]
WHERE (([forms]![filter]![outname] Is Null)
OR ([outlet data].[Outlet Name] = [forms]![filter]![outname]))
AND (([forms]![filter]![outaddress] Is Null)
OR ([outlet data].[Outlet Address] =
[forms]![filter]![outaddress]))
AND ...

When [forms]![filter]![outname] is in fact null, the first part of the
expression is True for all records, and so all are returned. If it is not
null, the OR part is evaluated, so only the matching records are returned.
The bracketing is important when you mix ANDs and ORs.

An alternative approach is to build the WHERE clause dynamically, from
only those controls where the user entered a search value. For an example
of how to do that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i created the following query:

SELECT [outlet data].[Out Nbr], [outlet data].[Outlet Name], [outlet
data].[Outlet Address], [outlet data].[Out City Name], [outlet data].[Out
State Code], [outlet data].[Out Zip Code]
FROM [outlet data]
WHERE ((([outlet data].[Outlet Name]) Like
nz([forms]![filter]![outname],"*")) AND (([outlet data].[Outlet Address])
Like nz([forms]![filter]![outaddress],"*")) AND (([outlet data].[Out City
Name]) Like nz([forms]![filter]![outcity],"*")) AND (([outlet data].[Out
Zip
Code]) Like nz([forms]![filter]![outzip],"*")));

What i want it to do is that when i update my search fields on my "filter
form" the query will return all the records that match that criteria in
that
field and return all records in a field if left blank.
When i update the fields on my forms, the query just goes blank, what am
i
doing wrong, i've done the exact same thing in another database and it
works
just fine. For some reason it doesnt want to work again. I also noticed
that
it works fine with two of the search fields: [outzip] and [outaddress]
but
not with [outname] and [outcity]. I would really appreciate any help.