Microsoft Access Query Input Criteria

Question

I am making a query for my database which contains a customer information table and payment table. I need to track monthly payments and contact those who have not paid by the end of each month. I am working on a query to output a list of all who have not paid during the month. People are able to pay for one month at a time or multiple months. I have a string field in my payment table where I record which month was paid for. The value could be, for example, "June" or "June, July, August" For my query I want to be able to input a given month and be able to see who has not paid.

I have made an input for the query where I can type in any month, and it will return payments for other months or when someone has not paid at all, but I am not able to return those whose values don't exactly match my input of "June" or any other single month. I am using the like function here. Here is my code:

SELECT Clients.[Player ID], Clients.[Player Name], Clients.[Contact Number], Payment.[Payment Amount], Payment.[Payment Month]
FROM Clients INNER JOIN Payment ON Clients.[Player ID] = Payment. [Player ID]
WHERE (((Payment.[Payment Month]) Not Like [Enter the month:])) OR (((Payment.[Payment Month]) Is Null));

When I run the query for an input of "June", it returns people who have done "June, July, August" as unpaid for the month of May. I know this is because "May, June, July" is different from the input value of "May." Is there a way that I can exclude these payments from the list of unpaid? I don't want the query to return any values that may include the input month. I was thinking that I may need to use a wildcard but I was unsure of how exactly to put this criteria in the query, or if I need to write any additional SQL code for it.

You should normalise your database, having a record for each paid year-month per customer. That would make your task a snap. — Gustav

June7 June7 · Accepted Answer · 2019-06-04T06:51:15

If this really is a string and not a multi-value field and normalizing is not an option at this time, try:

SELECT Clients.[Player ID], Clients.[Player Name], Clients.[Contact Number], Payment.[Payment Amount], Payment.[Payment Month]
FROM Clients INNER JOIN Payment ON Clients.[Player ID] = Payment. [Player ID]
WHERE (((Payment.[Payment Month]) Not Like "*" & [Enter the month:] & "*")) OR (((Payment.[Payment Month]) Is Null));

Advise not to use popup input parameters because cannot validate user input. Have user input value into control such as combobox on form and reference that control as parameter.

Microsoft Access Query Input Criteria

1 Answers