Forum Discussion
Having a date issue in a query
To be honest, I'm having trouble following the process, so before attempting an answer, can we get a bit of clarity.
You have a field called "PaymentDate". What does that represent? Is that the date a payment is actually made, or is it the date the next payment is due? Or???
You have a field called "PaySchedule". It appears to be the day of the month on which payments are due. Is that correct?
So, if the payment date is 1/6/2022 and the PaySchedule is "every 15th", why is the payment NOT on the 15th?
Also, the first line shows the number 6 in the PayScheduleDate, but the second line shows the number 8. However, the other two fields are identical. Why are rows 1 and 2 different?
Rather than dive right into examples, please explain why you need to do this. What is the goal? What is the process being modeled?
Thanks.
- I must've labled the fields bad. The payment date is the date the user made the payment. So basically I had the pay schedule text field entering things like "every 1st of the month". So what the user needs to do is just copy the number from the pay schedule field and it would subtract the date they made the payment from the numer that was entered. I.e. showing x days late/early.
- I must've labled the fields bad. The payment date is the date the user made the payment. So basically I had the pay schedule text field entering things like "every 1st of the month". So what the user needs to do is just copy the number from the pay schedule field and it would subtract the date they made the payment from the numer that was entered. I.e. showing x days late/early.
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:
