VBA - SQL request with filter

Question

I would like to do a SQL request in VBA that avoids some values.

First I do a request that give me the values I do not want:

I put those values in a table a for example: a = (1, 25, 3)
Then I would like to do a request like:

    rst.Source = "SELECT TableNumerosClients.NOM_CLIENT ," & _
                 "FROM table.Clients WHERE NOT table.id in a ;"

My problem here is a, I do not know how to make the query understand that a = (1, 25, 3)

Thanks

Lee Mac · Accepted Answer · 2018-11-27 22:46:35Z

2

Assuming that your example object a is genuinely a table and not an array of values, there are a couple of ways to accomplish this:

Using a subquery in the WHERE clause:

select c.nom_client
from clients c
where c.id not in (select a.id from a)

Using a LEFT JOIN:

select c.nom_client
from clients c left join a on c.id = a.id
where a.id is null

answered Nov 27, 2018 at 22:46

Lee Mac

16.1k6 gold badges38 silver badges84 bronze badges

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

1 Answer 1