Forum Discussion

Andy28348's avatar
Andy28348
Copper Contributor
Jan 11, 2022

Having a date issue in a query

Got a odd issue.  What I'm trying to accomplish is to have the user enter the date from what's printed from the text column PaySchedule in this example every 15th.  They would enter whatever date the Payment Date was i.e. 1/6/2022.   Then it would subtract the date they paid from the due date, the PayScheduleDate column.  For whatever reason it's not giving the correct numbers.  If I subtract 6 days from the Payment Date it should be 0 days but it's showing 31.  I tried adding 1 to the query and that didn't work either.  Not sure what the issue is.  I formatted the DateStatus field to only show "dd" not sure if that has anything to do with the issue.  I'm stuck.  Let me know if anyone has any suggestions for this. 

 

Access 2016
date and time
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Jan 11, 2022

    Andy28348 

     

    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.

    • Andy28348's avatar
      Andy28348
      Copper Contributor
      Jan 11, 2022
      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.
    • Andy28348's avatar
      Andy28348
      Copper Contributor
      Jan 11, 2022
      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.
      • George_Hepworth's avatar
        George_Hepworth
        Silver Contributor
        Jan 11, 2022

        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:

         

         

Resources