Days late on payment using Date()

  • Thread starter Thread starter jdbit2byte
  • Start date Start date
J

jdbit2byte

I need help trying to determine if a customer has made a payments on their
account within set amounts of time:

Example:
Current: Date() < 30 days since payment
30 Days: Date() >= 30 days and < 60 days since payment
60 Days: Date() >= 60 days and < 90 days since payment
90 Days: Date() >= 90 days and < 100 days since payment
Credit Hold: Date() >= 100 days since payment

There is also the calculation for if their Payments are Equal with their
purchases
(meaning there is no balance on their account)

I have tried lots of date math and formatting but keep getting all sorts of
errors. The code is lengthy so I can fill in the blanks if someone just
wants to give me an example.

Thanks,
JD
 
I'd create your own function for this, rather than struggling with something
in a Query.

Lets say PayDate contains the date when your customer last paid something
(it is a date, I hope, and not some crazy text string).

In a Module you could have a function like this:

Function AccStat(dtPay) As String

Dim MyDiff As Long

If Nz(dtPay) = 0 Then
'in case your field has nothing in it.
AccStat = ""
Exit Function
End If

MyDiff = Date - Format(dtPay, "0")

Select Case MyDiff

Case Is < 30
AccStat = "Current"
Case 30 To 59
AccStat = "30 Days"
Case 60 To 89
AccStat = "60 Days"
Case 90 To 99
AccStat = "90 Days"
Case Is >= 100
AccStat = "Credit Hold"

End Select

End Function

Now, in your Query, in Design View, you can type

CrStatus:AccStat(PayDate)

Evi
 
I have similar question (newbie here).I’m trying to create a query for “On
Time Deliveryâ€. I have query fields of PO, Date Received, Due Date and Date
Shipped. I’d like to calculate EARLY (shipped 3 days early from the due
date), On Time (Shipped on Due Date or 2 days early) and LATE (Shipped past
Due Date) and on a business days only (excluding Saturday and Sunday). These
fields are coming out of 3 tables. I would appreciate any ideas.

Thanks,

John
 
I’m trying to create a query for “On Time Deliveryâ€. I have query fields of
PO, Date Received, Due Date and Date Shipped. I’d like to calculate EARLY
(shipped 3 days early from the due date), On Time (Shipped on Due Date or 2
days early) and LATE (Shipped past Due Date) and on a business days only
(excluding Saturday and Sunday). These are all coming from 3 different
tables. I would appreciate any ideas.

Thanks,

John
 
John
Do you need to count Business Days Only for all the options or only when you
are calculating 'Late'?


Evi
 
Are you saying that if the order was due to be shipped on Saturday 04/08/07
but was actually shipped on Monday 06/08/07 it would be 'On Time'?
Evi
 
Hi John

You may have already found this function by David Nishimoto online. It
calculates the number of workdays between 2 days.
http://www.databasejournal.com/features/msaccess/article.php/1552691

Public Function WkDayDiff(sStartDate, sEndDate)
Dim iDays
Dim iWorkDays
Dim sDay
Dim i
'by David Nishimoto


iDays = DateDiff("d", sStartDate, sEndDate)

iWorkDays = 0

For i = 0 To iDays
'First day of the week is sunday
sDay = Weekday(DateAdd("d", i, sStartDate))
If sDay <> 1 And sDay <> 7 Then
iWorkDays = iWorkDays + 1
End If
Next
WkDayDiff = iWorkDays
End Function



Would you be able to adapt this with the code below to meet your needs?

Evi
 
Right, I've adapted it. This should give you the difference between 2 dates
but ignoring weekends. I haven't yet ironed out that if you want the
difference between a Saturday and the consecutive Sunday the answer is -1
instead of 0 but I think that it'll do for what you want John. Try it out.

Public Function WkDayDiff(dtStartDate, dtEndDate)
'EndDate - StartDate but ignoring Weekends
Dim iDays As Long
Dim iWorkDays As Long
Dim sDay As Integer
Dim i As Integer

On Error Resume Next


iDays = dtEndDate - dtStartDate
'number of ordinary days between the 2 dates.

iWorkDays = iDays
'starting value of iWorkdays
For i = 1 To iDays + 1
'cycle through the dates and take of one for every weekend.
sDay = Weekday(dtStartDate, vbMonday)
If sDay = 6 Or sDay = 7 Then
'ie the date is a weekend
iWorkDays = iWorkDays - 1
'take off the day if it's a weekend
End If
dtStartDate = dtStartDate + 1
Next i
WkDayDiff = iWorkDays
End Function

Evi
 
Back
Top