How to make an exception in SQL query (Access to Excel)

Question

I am taking some data from Access table to Excel worksheet and they depends some parameters. My data list on access table includes for example:

1-x branch
2-y branch
3-z branch
4-t commercial branch
5-r commercial branch
6-w corporate branch
7-g corporate branch

On Excel VBA if I want to retrieve all of my data, I am creating a SQL query like:

sql = "select * from [table] where [Branches] like "%"

if I want to retrieve only commercial branches data:

sql = "select * from [table] where [Branches] like '%" & "commercial" & "%'"

and if I want to retrieve only corporate branch data:

sql = "select * from [table] where [Branches] like '%" & "corporate" & "%'"

and here is the question. What am I have to do if I want to retrieve data except corporate and commercial branches? What sort of sql query can we create?

Thanks a lot.

It's my bad, I could explain more clear. Before creating the query I am using my parameters like this:

Select Case main.Range("D6").Value
Case "ALL"
brnch = "%"
Case "CORPORATE"
brnch = "%" & "CORPORATE" & "%"
Case "COMMERCIAL"
brnch = "%" & "COMMERCIAL" & "%"
End Select

and my query is actually like that:

sql = "select * from [table] where [Branches] like '" & brnch & "'"

now what can I do to retrieve branches without commercials and corporates.

Gordon's query will work. However, querying the data as you're doing by looking for certain keywords in each branch's name... that gets to be ugly, slow, and potentially inaccurate. Is there any chance that you could add an integer column to that table that indicates the type of Branch that each entity is? 1 = Branch, 2 = CommercialBranch, 3 = CorporateBranch. Then filtering your query becomes trivial: WHERE [Table].[BranchCategoryId] IN(1, 2) AND WHERE [Table].[BranchCategoryId] NOT IN(2, 3) — DBro
– DBro, Commented Apr 19, 2019 at 14:52
it would be good but unfortunately I can't. It is quite an old database and some other Excel Workbooks run with this db. — Cagatay
– Cagatay, Commented Apr 19, 2019 at 15:01
So your Excel cell , main.Range("D6").Value, will have entries for NO commmercials and corporates? — Parfait
– Parfait, Commented Apr 19, 2019 at 18:08
I got an entry like "Others" in order to select no commercials and corporates. But I do not know how to create proper query. — Cagatay
– Cagatay, Commented Apr 24, 2019 at 9:01

Gordon Linoff · Accepted Answer · 2019-04-19 14:43:24Z

2

You can use not like:

sql = "select * from [table] where Branches not like '%corporate%' and branches not like '%commercial%'";

If you want to run this code in MS Access, the wildcards are different and the string delimiter is a double quote:

sql = "select * from [table] where Branches not like ""*corporate*"" and branches not like ""*commercial*""";

answered Apr 19, 2019 at 14:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cagatay Over a year ago

Thank you very much but I made an additon to my problem below.

Collectives™ on Stack Overflow

How to make an exception in SQL query (Access to Excel)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related