Can't figure out query with multiple filtering options

[utmigs]

I have a query that involves nearly 10 fields. I have a form that I use to input criteria for the query. My problem is that when I enter criteria for multiple fields, I get more records than what I ask for. For instance, if I ask for all Buildings that have Street Numbers between 2000 and 4000, it returns all properties (raw land & buildings) with Street Numbers between 2000 and 4000. I'm pretty sure the problem is in the Or statement, but I can't get it to work with the And statement.

SELECT [Property List].[Property ID], [Property List].[Property Type], [Property List].[Street Number]
FROM [Property List]
WHERE (
(([Property List].[Property ID] Like [Forms]![Query Criteria]![Text39]) Or ([Forms]![Query Criteria]![Text39] Is Null))
Or
(([Property List].[Property Type] Like [Forms]![Query Criteria]![Text41]) Or ([Forms]![Query Criteria]![Text41] Is Null))
Or
((([Property List].[Street Number]) Between [Forms]![Query Criteria]![Text42] And [Forms]![Query Criteria]![Text56]) Or ([Forms]![Query Criteria]![Text42] Is Null) Or ([Forms]![Query Criteria]![Text56] Is Null)) 
);

Also, remember there are seven other fields with the same thing. I've shortened it to post it. Please help if you can. Thanks.

 

[calmwind]

Wouldn't you have to do something like 4! since there are 4 fields of interest?

Basically think of your input fields as having having data (1) or not having data (0). You would end up with 16 possible scenerios:
0000
1000
0100
0010
0001
1100
1010
1001
0110
0101
0011
1110
1011
1101
0111
1111

Here's some code
SELECT PropertyList.PropertyID, PropertyList.PropertyType, PropertyList.StreetNumber
FROM PropertyList
WHERE (((PropertyList.PropertyID) Like [Forms]![Query Criteria]![Text39]) AND ((PropertyList.PropertyType) Like [Forms]![Query Criteria]![Text41]) AND ((PropertyList.StreetNumber) Between [Forms]![Query Criteria]![Text42] And [Forms]![Query Criteria]![Text56])) OR (((PropertyList.PropertyType) Like [Forms]![Query Criteria]![Text41]) AND ((PropertyList.StreetNumber) Between [Forms]![Query Criteria]![Text42] And [Forms]![Query Criteria]![Text56]) AND (([Forms]![Query Criteria]![Text39]) Is Null)) OR (((PropertyList.PropertyID) Like [Forms]![Query Criteria]![Text39]) AND ((PropertyList.StreetNumber) Between [Forms]![Query Criteria]![Text42] And [Forms]![Query Criteria]![Text56]) AND (([Forms]![Query Criteria]![Text41]) Is Null)) OR (((PropertyList.PropertyID) Like [Forms]![Query Criteria]![Text39]) AND ((PropertyList.PropertyType) Like [Forms]![Query Criteria]![Text41]) AND ((PropertyList.StreetNumber)<[Forms]![Query Criteria]![Text56]) AND (([Forms]![Query Criteria]![Text42]) Is Null)) OR (((PropertyList.PropertyID) Like [Forms]![Query Criteria]![Text39]) AND ((PropertyList.PropertyType) Like [Forms]![Query Criteria]![Text41]) AND ((PropertyList.StreetNumber)>[Forms]![Query Criteria]![Text42]) AND (([Forms]![Query Criteria]![Text56]) Is Null)) OR (((PropertyList.StreetNumber) Between [Forms]![Query Criteria]![Text42] And [Forms]![Query Criteria]![Text56]) AND (([Forms]![Query Criteria]![Text39]) Is Null) AND (([Forms]![Query Criteria]![Text41]) Is Null)) OR (((PropertyList.PropertyID) Like [Forms]![Query Criteria]![Text39]) AND ((PropertyList.StreetNumber)<[Forms]![Query Criteria]![Text56]) AND (([Forms]![Query Criteria]![Text41]) Is Null) AND (([Forms]![Query Criteria]![Text56]) Is Null)) OR (((PropertyList.PropertyID) Like [Forms]![Query Criteria]![Text39]) AND ((PropertyList.StreetNumber)>[Forms]![Query Criteria]![Text42]) AND (([Forms]![Query Criteria]![Text41]) Is Null) AND (([Forms]![Query Criteria]![Text42]) Is Null)) OR (((PropertyList.PropertyType) Like [Forms]![Query Criteria]![Text41]) AND ((PropertyList.StreetNumber)<[Forms]![Query Criteria]![Text56]) AND (([Forms]![Query Criteria]![Text39]) Is Null) AND (([Forms]![Query Criteria]![Text56]) Is Null)) OR (((PropertyList.PropertyType) Like [Forms]![Query Criteria]![Text41]) AND ((PropertyList.StreetNumber)>[Forms]![Query Criteria]![Text42]) AND (([Forms]![Query Criteria]![Text39]) Is Null) AND (([Forms]![Query Criteria]![Text42]) Is Null)) OR (((PropertyList.PropertyID) Like [Forms]![Query Criteria]![Text39]) AND ((PropertyList.PropertyType) Like [Forms]![Query Criteria]![Text41]) AND (([Forms]![Query Criteria]![Text42]) Is Null) AND (([Forms]![Query Criteria]![Text56]) Is Null)) OR (((PropertyList.PropertyID) Like [Forms]![Query Criteria]![Text39]) AND (([Forms]![Query Criteria]![Text41]) Is Null) AND (([Forms]![Query Criteria]![Text42]) Is Null) AND (([Forms]![Query Criteria]![Text56]) Is Null)) OR (((PropertyList.PropertyType) Like [Forms]![Query Criteria]![Text41]) AND (([Forms]![Query Criteria]![Text39]) Is Null) AND (([Forms]![Query Criteria]![Text42]) Is Null) AND (([Forms]![Query Criteria]![Text56]) Is Null)) OR (((PropertyList.StreetNumber) Between [Forms]![Query Criteria]![Text42] And [Forms]![Query Criteria]![Text56]) AND (([Forms]![Query Criteria]![Text39]) Is Null) AND (([Forms]![Query Criteria]![Text41]) Is Null)) OR ((([Forms]![Query Criteria]![Text39]) Is Null) AND (([Forms]![Query Criteria]![Text41]) Is Null) AND (([Forms]![Query Criteria]![Text42]) Is Null) AND (([Forms]![Query Criteria]![Text56]) Is Null)) OR (((PropertyList.PropertyID) Like [Forms]![Query Criteria]![Text39]) AND (([Forms]![Query Criteria]![Text41]) Is Null) AND (([Forms]![Query Criteria]![Text42]) Is Null) AND (([Forms]![Query Criteria]![Text56]) Is Null));

hope that helps

 

[Jon K]

SELECT [Property ID], [Property Type], [Street Number]
FROM [Property List]
WHERE ([Property ID]=Forms![Query Criteria]!Text39 Or Forms![Query Criteria]!Text39 Is Null)=True
And ([Property Type]=Forms![Query Criteria]!Text41 Or Forms![Query Criteria]!Text41 Is Null)=True
And ([Street Number] Between Forms![Query Criteria]!Text42 And Forms![Query Criteria]!Text56 Or Forms![Query Criteria]!Text42 Is Null Or Forms![Query Criteria]!Text56 Is Null)=True;


Note the brackets and the =True. This way you can easily add criteria for other fields (even if you subsequently switch the query to Design View and save the query there.)


Note:
Since you don't use wildcard characters, there is no need to use the Like operator. The = sign is more efficient.

It is better to name the text boxes txtPropertyID, txtProperType etc.

You may consider using a combo box for Property Type.

It is generally considered bad practice to include embedded spaces in field names.
.

 

[utmigs]

I tried both methods, and I couldn't get either one to return any records. Thank you both for taking the time though. I really, really appreciate it. It just seems like there should be some easier way to perform this type of query.

 

[Jon K]

I have attached a sample database, which contains the query from my previous post.

You can open the form, type Buildings in the Property Type box, 2000 in the From box, 4000 in the To box, and click on the command button to run the query.
.

 

[utmigs]

I think that will work for what I'm doing. Thank you very much. That really helped me out. Again, I appreciate it.