Search Function with queries

[Guest]

Hi guys,
new user to access 2000 and i am trying to create a search
query that will allow me to enter in a value and return that value. The part
i am having trouble with is i need to do this for many fields and if i use
the method i came up with, when i leave a entry empty using the "AND"
function i get nothing and when i use the "OR" function i get all the values
for both. maybe you guys can help me get in the right direction.

this is the command i am using in SQL:
SELECT [Chain Machine prod Machine list].Description, [Chain Machine prod
Machine list].Manufacturer, [Chain Machine prod Machine list].[Model No#],
[Chain Machine prod Machine list].[Serial No#], [Chain Machine prod Machine
list].[Gold Tag], [Chain Machine prod Machine list].Dept, [Chain Machine prod
Machine list].Location, [Chain Machine prod Machine list].CAPACITY
FROM [Chain Machine prod Machine list]
WHERE ((([Chain Machine prod Machine list].Dept)=[INSERT DEPT:])) OR
((([Chain Machine prod Machine list].Location)=[INSERT Location:])) OR
((([Chain Machine prod Machine list].Dept)=[INSERT DEPT:])) OR ((([Chain
Machine prod Machine list].Location)=[INSERT Location:]));

 

[Guest]

If you want all the records to return if the criteria is not enterd then try
something like

SELECT [Chain Machine prod Machine list].Description, [Chain Machine prod
Machine list].Manufacturer, [Chain Machine prod Machine list].[Model No#],
[Chain Machine prod Machine list].[Serial No#], [Chain Machine prod Machine
list].[Gold Tag], [Chain Machine prod Machine list].Dept, [Chain Machine prod
Machine list].Location, [Chain Machine prod Machine list].CAPACITY
FROM [Chain Machine prod Machine list]
WHERE ([Chain Machine prod Machine list].Dept=[INSERT DEPT:] OR [INSERT
DEPT:] Is Null ) And ([Chain Machine prod Machine list].Location=[INSERT
Location:] OR [INSERT Location:] Is Null)

 

[Lynn Trapp]

You will need to use a combination of both AND and OR like this:

WHERE ([Chain Machine prod Machine list].Dept=[INSERT DEPT:] OR [INSERT
DEPT:] IS NULL)
AND
([Chain Machine prod Machine list].Location=[INSERT Location:] OR [INSERT
Location:] IS NULL);

You can also enhance the search by using LIKE instead of =

[Chain Machine prod Machine List].Dept LIKE "*" & [INSERT DEPT:] & "*"

 

[Douglas J. Steele]

SELECT [Chain Machine prod Machine list].Description, [Chain Machine prod
Machine list].Manufacturer, [Chain Machine prod Machine list].[Model No#],
[Chain Machine prod Machine list].[Serial No#], [Chain Machine prod Machine
list].[Gold Tag], [Chain Machine prod Machine list].Dept, [Chain Machine
prod
Machine list].Location, [Chain Machine prod Machine list].CAPACITY
FROM [Chain Machine prod Machine list]
WHERE ([Chain Machine prod Machine list].Dept=[INSERT DEPT:] OR [INSERT
DEPT:] IS NULL) AND
([Chain Machine prod Machine list].Location=[INSERT Location:] OR [INSERT
Location:] IS NULL) AND
([Chain Machine prod Machine list].Dept=[INSERT DEPT:] OR [INSERT DEPT:] IS
NULL) AND
([Chain Machine prod Machine list].Location=[INSERT Location:] OR [INSERT
Location:] IS NULL)