Re: Days late on payment using Date()

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


Evi

"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:ONfCi0mbIHA.1132@xxxxxxxxxxxxxxxxxxxxxxx
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





"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

  • Re: Days late on payment using Date()
    ... I have query fields of PO, Date Received, Due Date and Date Shipped. ... AccStat = "Current" ... > I need help trying to determine if a customer has made a payments on> their ...
    (microsoft.public.access.reports)
  • Re: Days late on payment using Date()
    ... I have query fields of PO, Date Received, Due Date and Date Shipped. ... AccStat = "Current" ... > I need help trying to determine if a customer has made a payments on> their ... > (meaning there is no balance on their account) ...
    (microsoft.public.access.reports)
  • Re: Days late on payment using Date()
    ... AccStat = "Current" ... Now, in your Query, in Design View, you can type ... Date< 30 days since payment ... (meaning there is no balance on their account) ...
    (microsoft.public.access.reports)
  • Re: Days late on payment using Date()
    ... AccStat = "Current" ... Now, in your Query, in Design View, you can type ... Date< 30 days since payment ... (meaning there is no balance on their account) ...
    (microsoft.public.access.reports)