MS Access SQL Code - Query Issue (NOT IN)

Question

I'm trying to find out which partners has not paid the monthly tuition in a particular month

I have a table called Socios contaning all partners names SocioNome and another table called RegistroPagamento contaning all payments done (This particular table is fullfiled by a form where the user input the Partner Name, Amount Paid and which particular month/year the payment is related to)

I have created a query where I used the SQL Code below

SELECT [SocioNome]
FROM [Socios] NOT IN
(SELECT [SocioNome] FROM [RegistroPagamento] WHERE [MesBoleto] = [Forms]![Selecionar_MCobranca]![TBoxMes] AND [AnoBoleto] = [Forms]![Selecionar_MCobranca]![TBoxAno]);

[Selecionar_MCobranca] is the form I have mentioned before and the [TBoxMes] & [TBoxAno] are the combo boxes from the form which the user can select the month and the year the payment reffers to.

When I run the code, a error message pops up indicating that there is a FORM clause syntax issue, and I dont know exactly what is causing the problem

Any help is highly appreciated

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-04-26T19:36:03

NOT IN is a comparison operator in the WHERE clause. It does not belong in the FROM cluase. I strongly recommend using NOT EXISTS instead. The idea is:

SELECT s.SocioNome
FROM Socios as s
WHERE NOT EXISTS (SELECT 1
                  FROM RegistroPagamento as rp
                  WHERE rp.MesBoleto = [Forms]![Selecionar_MCobranca]![TBoxMes] AND
                        rp.AnoBoleto = [Forms]![Selecionar_MCobranca]![TBoxAno] AND
                        rp.SocioNome = s.SocioNome
                 );

NOT IN returns no rows if any row in the subquery is NULL. To protect against this, just use NOT EXISTS. It has the expected behavior in this case.

MS Access SQL Code - Query Issue (NOT IN)

1 Answers