SQL Where statement from fields

[Steve400]

Hi,
I'm having trouble with using a where statement linked to fields when the field is empty. I need a way to say if field is null then 'do nothing'/'select all' else use the text from the box.

I have a form (ServicesRCSSearch) which has 3 combo boxes (Location1, Location2 and Location3). These fields are linked to a query. The button on the form generates the query.

My SQL for the query is currently:
Select Services.Key, Services.Location, Services_1.Location, Services_2.Location
From Services, Services_1, Services_2 (copies of the same table all left joined)

Where
((IIf(forms!ServicesRCSSearch!Location1 Is Null,"",services.Location=forms!ServicesRCSSearch!Location1))<>False)

And ((Services_1.Location)=IIf(forms!ServicesRCSSearch!Location2 Is Null,forms!ServicesRCSSearch!Location1,forms!ServicesRCSSearch!Location2))

And ((Services_2.Location)=IIf(forms!ServicesRCSSearch!Location3 Is Null,forms!ServicesRCSSearch!Location1,forms!ServicesRCSSearch!Location3))


This works in that it uses the fields to filter the query but when Location1 is empty there are no results as you can see from the code.

I'd love some help, it's driving me nuts.
Thanks

 

[Steve400]

Ok thanks,
The query now seems to be running too slow to load.
Do you think this would be due to having the same table 3 times? Is there a better way to design the query

 

[Steve400]

Thanks and yes the desired out come is:
When fields are blank - return all records.
When 1 location field has data - limit records to the location.
When multiple fields have have - limit records of those with all selected locations.

e.g. if the table data is as follows and the form selects Ireland and China, the query should only return ID A.

ID Location
A Ireland
B China
A China
D England
B USA

Currently the form is returning IDs with either Ireland OR China, not AND, so I'd be getting ID A and B.

Is this just a case of playing with the ORs and AND in the syntax?