Re: Days late on payment using Date()

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


"Evi" <evwool@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:pShtj.346$g81.211@xxxxxxxxxxxxxxxxxxxxxxx
Oops, Should have tried it out first. It won't do what you want.
Evi

"Evi" <evwool@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:dIhtj.440$St5.53@xxxxxxxxxxxxxxxxxxxxxxx

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


"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:DE415546-C1BA-43C1-9E2C-450B55898C52@xxxxxxxxxxxxxxxx
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





"jdbit2byte" <jdbit2byte@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7751CAA6-873F-4DD4-84C8-BF3E367C09A9@xxxxxxxxxxxxxxxx
Thanks for the help, it worked great.

Thanks again,
JD


"Evi" wrote:

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" <jdbit2byte@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:8F51217A-E686-436F-81C7-D1BB6F0B09E7@xxxxxxxxxxxxxxxx
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
















.

Relevant Pages