Having a date issue in a query

Andy28348 

Not really bad labelling, just that the work flow and business rules behind it wasn't obvious to someone seeing it for the first time.

So you want to calculate how many days early or late a payment was made, based on whether the payment date is before or after the scheduled due date.

Here's how I'd do that, convert the current values from things like "Every 15th" to simply 15, and change that to a number field from a text field. That is, in fact, how you have "PayScheduleDate".

But that creates another ambiguity for me. In the first record one field says "every 15th" but a PayScheduleDate of 6. Is it due on the 15th or on the 6th?

Otherwise you have relatively straightforward math. Once you resolve this, the math would be 

DaysEarlyOrLate= DateDiff("dd", PaymentDate, DateSerial(Year(PaymentDate), Month(PaymentDate), PaySchedule))

That calculates the number of days between two dates, the first being the actual PaymentDate and the second being the date calculated by inserting the scheduled day of the month for that month.

I see a problem, though, still. If a payment is late (or early) beyond the end of the current month, or the beginning of the current month, it'll be off. In other words, if someone completely misses a monthly payment and pays during the next month, you have to account for that too, I assume, but deciding WHICH scheduled payment any given amount paid should apply to. Is that in your current work flow somehow?

For example: