Re: LIKE in SQL and substring

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.
.

Relevant Pages

  • Re: LIKE in SQL and substring
    ... 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. ... Access MVP 2002-2005, 2007-2008 ... "John Spencer" wrote: ... Exxonmobil comm ...
    (microsoft.public.access.modulesdaovba)
  • Re: LIKE in SQL and substring
    ... Thanks John, but not all names would have COMM in their name. ... Exxonmobil comm ... But the LIKE query matches them both as ...
    (microsoft.public.access.modulesdaovba)
  • Re: LIKE in SQL and substring
    ... "John Spencer" wrote: ... Access MVP 2002-2005, 2007-2008 ... Exxonmobil comm ... But the LIKE query matches them both as ...
    (microsoft.public.access.modulesdaovba)
  • Re: Exporting from Access 2002 to Excel 2002 into multiple tabs
    ... and it highlights the first line of code "DIM qdf As DAO.QueryDef". ... you don't need a separate query for what you seek to do. ... <MS ACCESS MVP> ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.queries)
  • Re: Exporting from Access 2002 to Excel 2002 into multiple tabs
    ... and it highlights the first line of code "DIM qdf As DAO.QueryDef". ... you don't need a separate query for what you seek to do. ... <MS ACCESS MVP> ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.queries)