Query pulling from multiple tables with multiple criteria

[Knape97]

Hi Guys

I am trying to run a query that will select all the fields related to a Property (Service) that is based within a user specified county. I then need to have the query also show any tenants associated with the services that are NOT set as NLU (no longer using)

Anywho, I can get the query to show all records associated with a service (including service users) when only county is specified as a Criteria using the following code;

SELECT Tbl_Services.ServiceName, Tbl_Services.ServiceType, Tbl_Services.ServiceCounty, Tbl_Services.NoPeople, Tbl_Services.ContractType, Tbl_Services.ContractHours, Tbl_Services.LandLord, Tbl_Users.UserName, Tbl_Users.DateOfBirth, Tbl_Users.UserSex, Tbl_Users.UserNI, Tbl_Users.SPRef, Tbl_Users.Status
FROM Tbl_Services INNER JOIN Tbl_Users ON Tbl_Services.ServiceID = Tbl_Users.UserService
WHERE (((Tbl_Services.ServiceCounty) Like "*" & [County] & "*"));


My problem comes if I add in the second criteria for the user status. Using the following code i get the error message;

"The Expression is typed incorrectly, or is too complex to be evaluated."

SELECT Tbl_Services.ServiceName, Tbl_Services.ServiceType, Tbl_Services.ServiceCounty, Tbl_Services.NoPeople, Tbl_Services.ContractType, Tbl_Services.ContractHours, Tbl_Services.LandLord, Tbl_Users.UserName, Tbl_Users.DateOfBirth, Tbl_Users.UserSex, Tbl_Users.UserNI, Tbl_Users.SPRef, Tbl_Users.Status
FROM Tbl_Services INNER JOIN Tbl_Users ON Tbl_Services.ServiceID = Tbl_Users.UserService
WHERE (((Tbl_Services.ServiceCounty) Like "*" & [County] & "*") AND ((Tbl_Users.Status)<>"NLU"));

Any help would be greatly appreciated

 

[PHV]

And this ?
WHERE Tbl_Services.ServiceCounty Like "*" & [County] & "*" AND Tbl_Users.Status<>"NLU";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Knape97]

Didnt quite understand that last msg? The SQL posted is exactly what Access produced, are you suggesting that I remove the brackets?