Re: LIKE in SQL and substring

So if the company name ends in " comm" you don't want the record?

Perhaps the following will give you the results you want.
SELECT *
FROM tbl_Comm_FBO
WHERE tbl_Comm_FBO.ICAO='EGHH'
AND tbl_Comm_FBO.CommFBO Like 'EXXONMOBIL*"
AND tbl_Comm_FBO.CommFBO NOT Like '* comm'


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Tina wrote:
The quotes is not an issue.
The commercial is a table of all commercial ones. No other way to identify them as such. The issue is that there are two records.

1. Exxonmobil 2. Exxonmobil comm

First is not commercial, second is. But the LIKE query matches them both as commercial.

I have five data records, of which two are as above. I match these data records against commercial table to flag commercial. Only one of five (2 above) should be flagged but both do. It seems to me that the query syntax is doing what it should, i.e. exxonmobil% will find both. But are there other ways to do the query?

I am in VBA in Excel so my SQL has % instead of *. And I just created an identical query in Access to post for here.

"wentex" wrote:

Is there a reason you have (single quote) 'EGHH' but (double quote) "EXXONMOBIL*"?

"John Spencer" wrote:

I'm missing something in your description. How do you know a particular records is "commercial"? Is there a field that tells you this?

Your query doesn't filter records on anything that looks like it would tell if the returned records were "commercial" or not.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


omsoft wrote:
I have a query to get data from Access table.
It looks at the product code and checks against a table to see if there is a match. If there is, it flags it as commercial and returns true. Else returns false.

SELECT *
FROM tbl_Comm_FBO
WHERE (((tbl_Comm_FBO.ICAO)='EGHH') AND ((tbl_Comm_FBO.CommFBO) Like "EXXONMOBIL*"));

Then I do further processing if it is not commercial.
One of the entry is "EXXONMOBIL" and that is not commercial, but the above query marks it as such. It marks two entries as commercial. One correctly and another not.

Does anyone know how do I fix the query so it does not.

Thanks.
.

Quantcast