How to enter Query criteria that has & in the name (1 Viewer)

[cMcDowell23]

Back story: In my database I have created a query that pulls jobs based on the status (In Progress, Invoiced, Completed, etc.) and then a 2nd query to further refine it by the location of the job (Downtown, In Town and ITBNDT & OOT [In town but not downtown & out of town]). It is taking the criteria of "ITBNDT & OOT" but not pulling anything with I run the query.

Is there anything I am missing or have to change in order for it to pull those records?

 

[XPS35]

I think you should use AND instead of &.

 

[June7]

Are you saying & character is in the data (filter criteria for this works just fine for me)? Or are you trying to reference two fields with concatenation?

Post your attempted SQL statement.

 

[cMcDowell23]

I solved my issue! I think it was an issue with my INNER JOIN. I changed it to LEFT JOIN and we were good!

Thanks for all your help!

 

[The_Doc_Man]

For future reference, using special characters in any object name is a poor choice. The problem is that there are circumstances where quoted items can be passed as arguments in a way that they will lose quotes and expose the inner characters.

Therefore, a name like "ITBNDT & OOT" works OK as long as you scrupulously keep it quoted. But if you pass that as an argument to a subroutine or function as a name string, it is NOT necessarily quoted in the called code unless you re-establish the quoting. And the moment that name is exposed WITHOUT quotes it looks like a concatenation of two shorter names. So you may indeed have gotten this to work, and you are to be congratulated for your success. But watch out for the implications of this down the road.

 

[June7]

Doc, I think "ITBNDT & OOT" is data, not field name.