Using Query Builder Criteria line to create an Exception Report

[Jamescdawson]

I'm trying to generate an Exceptions Report which displays a list of clients that have missed a monthly payment.



I'm starting with a basic client list which returns all payments that each client has made over the previous year, but I now want to omit clients that have made a payment within the last 30 days, leaving just the clients that have NOT made a payment within that 30 days.

Payments and payment dates are recorded each day in a "Payments Received" table.

I've tried a few options in the 'Criteria' line in the Date Received field in the Query Builder without success.

Do I have to create an expression or can I use an expression in the criteria line of the "Date Received" field on the Query Builder?

I hope this makes sense !!!

Jimbo

[pbaldy]

Generally I'd create a query that returned those that HAD made a payment in the last 30 days, then used the unmatched query wizard to compare that against a table/query of all customers. That should return the customers who have NOT made a payment in the last 30 days. An alternative could be a totals query with a max on the date field, with a criteria of

< Date() - 30

but that would miss people with no payments.

[Jamescdawson]

Hi Paul,
Long time since we did this.... Many Thanks.... I tried the < Date() - 30 but got the syntax wrong. I'll try again

I am not familiar with the unmatched query wizard !

Jimbo

[pbaldy]

It's a wizard. It will lead you step by step after you create the first query.

[Jamescdawson]

I have a date selection arrangement set up for my reports where I enter dates on a form and then hit a button to generate the report.... Do you think there is a way that I can use my Date selector to select the dates for the last 30 days 8/12/15 - 8/1/16 and the report return the null data use an expression in the query builder ? Or is my logic messed up?