Days late on payment using Date()

[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

 

[Evi]

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

 

[jdbit2byte]

Thanks for the help, it worked great.

Thanks again,
JD

 

[John Torres]

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

 

[John Torres]

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

 

[Evi]

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


Evi

 

[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

 

[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

 

[Evi]

Oops, Should have tried it out first. It won't do what you want.
Evi

 

[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