LIKE in SQL and substring

  • Thread starter Thread starter omsoft
  • Start date Start date
O

omsoft

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.
 
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
'====================================================
 
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.
 
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
'====================================================
 
Thanks John, but not all names would have COMM in their name. That would be
too easy.
I have a table which has all commercial entities.

I just compare on two fields, one is ICAO and another is FBO name.
These two values come from user input. Before doing any calculations I want
to flag commercial entities and exclude them. There is no patterns anywhere
that I can match.
 
If there is nothing within a record that can identify it as being
"commercial" then there is no way that you can extract just the records
you want using a query.

You have to be able to identify the record you want by the content of
the record.

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