Multiple Criteria Query

[Accessosaurusrex]

I have a table with 14 fields. I am looking to pull up records based first on a user input to match one field and then all records that meet a criteria of three other potential fields.

My code is as follows:
SELECT Bcone.Job, Bcone.ID, Bcone.Customer, Bcone.Desc, Bcone.Code, Bcone.Element, Bcone.Mark, Bcone.Weld, Bcone.RorRR, Bcone.Root, Bcone.Dim, Bcone.Grd, Bcone.Rad, Bcone.Loc, Bcone.Notes, Bcone.WDate, Bcone.WTime, Bcone.Re, Bcone.ClrDate, Bcone.RX, Bcone.NS
FROM Bcone
WHERE (((Bcone.Job)=[Enter Job]) AND ((Bcone.Re)="-1")) OR (((Bcone.RX)="-1")) OR (((Bcone.NS)="-1"))
ORDER BY Bcone.Job, Bcone.Code, Bcone.Element, Bcone.Mark, Bcone.Weld;

This fails me as I get all "jobs" where any of the or criteria are functioning. I simply want any of the records with the job input from the user that meet any of the other three criteria to return.

Thanks for any assistance

 

[Alansidman]

WHERE (((Bcone.Job)=[Enter Job]) AND ((Bcone.Re)="-1")) OR (((Bcone.RX)="-1")) OR (((Bcone.NS)="-1"))

Are the fields Re, RX and NS text fields? If they are numbers (format in the table?), then remove the quotation marks.

 

[Accessosaurusrex]

I changed them over to number fields and removed the quotations so now it looks like this:
WHERE (((Bcone.Job)=[Enter Job]) AND ((Bcone.Re)=-1)) OR (((Bcone.RX)=-1)) OR (((Bcone.NS)=-1))

However it still does not give the return I am hoping to achieve. I need to separate the query into two steps, one where it finds only records with the job that is entered and then where any of those records meet any of the remaining three criteria. Not sure how to get there from here but will keep working at it.

 

[Alansidman]

Make your second query using the first as the record set. Then you will only have to run the second query as it is dependent on the first.

 

[vbaInet]

Replace the WHERE line with this:

WHERE [COLOR=Blue]([/COLOR]Bcone.Job=[Enter Job][COLOR=Blue])[/COLOR] AND [COLOR=Red]([/COLOR](Bcone.Re="-1") OR (Bcone.RX="-1") OR (Bcone.NS="-1")[COLOR=Red][B])[/B][/COLOR]

Notice how all the OR parts are grouped within the red parentheses.

 

[Alansidman]

@vbaInet--
I really like it when I find out something new. Thanks for the SQL lesson here.

 

[vbaInet]

Always happy to be of service Alan