Re: On Time Delivery Query

John,
IIf([ShipDate]<=DateAdd("d",-3,[DueDate]),"Early",IIf([ShipDate]>=DateAdd("d",-2,[DueDate])
And [ShipDate]<=DateAdd("d",+2,[DueDate],"On Time","Late"))

Says...
If ShippedDate is 3 days or more less than DueDate = Early
If ShippedDate is between DueDate -2 days before and DueDate + 2 days after
= OnTime
Anything else... Late

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:D8632CC7-69C1-4853-9633-C37640D92897@xxxxxxxxxxxxxxxx
Hi Al,
Sorry about the confusion, I didn't understand the logic behind it now I
understand. My question should have been how do you add 2 days later on
the "On Time". On Time=Shipped on DueDate or 2 days early and 2 days later
from due date.

Currently what I have is "On Time (Shipped on Due Date or 2 days early)".
I tried adding +2 in expressions (...("d",-2,+2,DueDate])......That didn't
work.
Thanks again! Apprciate it so much!
John

IIf([ShipDate]<=DateAdd("d",-3,[DueDate]),"Early",IIf([ShipDate]>=DateAdd("d",-2,[DueDate])
And [ShipDate]<=[DueDate],"On Time","Late"))



"Al Campagna" <newsgroups@xxxxxxxxxxx> wrote in message
news:edwn6ElcIHA.6024@xxxxxxxxxxxxxxxxxxxxxxx
John,
First, you don't indicate exactly what code you tried, and... "doesn't
work" is not descriptive of what really happened.
You wrote...
If I want to change my criteria of Late (Late = past due date and + 2
days
from due date) how do i write that?
Where did the Past Due Date come from? That's not in your original
list of fields.
Example dates and example Statuses would also be helpful, as I do not
understand what you're saying about the "Late" logic.
------------------
There is no calculation in the IIF Statement as to LATE. My logic
states...
"If a ShipDate is NOT "Early", and a ShipDate is NOT "On Time"... then
it must be "Late"
That's the "fallout" (no logic satisfied) of the IIF statement.

Try adjusting your "Early" and/or "OnTime" logic to cause the IFF to
properly fallout to "Late"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:B973A220-CFA5-4C55-8DDF-55912E84B9C1@xxxxxxxxxxxxxxxx
Al,
If I want to change my criteria of Late (Late = past due date and + 2
days from due date) how do i write that? I tried to modify and it
does'nt work.......
Here's the original code:
IIf([ShipDate]<=DateAdd("d",-3,[DueDate]),"Early",IIf([ShipDate]>=DateAdd("d",-2,[DueDate])
And [ShipDate]<=[DueDate],"On Time","Late"))
Thanks again.
John


"Al Campagna" <newsgroups@xxxxxxxxxxx> wrote in message
news:%23%23FroBAcIHA.1376@xxxxxxxxxxxxxxxxxxxxxxx
John,
I just noticed a mistype in my last post...
that will yield a count of Early in that group.
Should have read...
that will yield a count of "On Time" in that group.

Glad you got going OK...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:56DF8722-1A54-4DF6-961F-C87DEC80379D@xxxxxxxxxxxxxxxx
Thanks again - it works. Thanks so much for your help!

"Al Campagna" <newsgroups@xxxxxxxxxxx> wrote in message
news:%23vo38A3bIHA.4684@xxxxxxxxxxxxxxxxxxxxxxx
John,
The totals for each group should be done on the report footers,
not the query.

Place an unbound Text Control (ex. name = totEarly), in whatever
Group Footer section you want totals for.
Make the ControlSource of that text control...
= Sum(IIf([Status]="On Time",1,0))
that will yield a count of Early in that group.

Repeat that process with another unbound text control (ex. name =
totAll), with a ControlSource of...
= Sum(*)
that will yield a count of all records in that group.

If you still have problems, I need you to explain exactly what you
did, where you did it, and the code involved.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:OIEKrSybIHA.1376@xxxxxxxxxxxxxxxxxxxxxxx
I didn't work with the part where =Sum(IIf([Status]="On Time",1,0)).
It shows as a text when you preview the report. Same thing with the
=Count(*). Sorry I'm a newbie. I probably misunderstood some of the
details.
Thanks again and I will try it again.
John

"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:82098A0F-D849-4E1C-98E0-32A687444E0F@xxxxxxxxxxxxxxxx
THANK YOU VERY MUCH.
I will try it tomorrow morning- I appreciate it so much!!!
John

"Al Campagna" <newsgroups@xxxxxxxxxxx> wrote in message
news:ezcwptobIHA.1164@xxxxxxxxxxxxxxxxxxxxxxx
John,
You could apply my calculation and bind the value to a field
name like ShipStatus.
In your query design grid...
ShipStatus :
IIf([ShipDate]<=DateAdd("d",-3,[DueDate]),"Early",IIf([ShipDate]>=DateAdd("d",-2,[DueDate])
And [ShipDate]<=[DueDate],"On Time","Late"))

Place the "bound" field ShipStatus on your report.
In any legitimate Footer, this would yield the Early count for
whatever Group.
=Sum(IIf([Status]="On Time",1,0))
Use that for each ShipStatus value.

Now, in each group, =Count(*) would yield the total count of all
records.
So... given that you know how many records there are, and the
count of each ShipStatus type, you can now calculate the
percentages of each ShipStatus.
Which (I'll bet a nickel) is probably what you're really looking
for after all...

Given what I know about this report, I would consider breaking
out the Status values... each in it's own column.
Early
Early
Late
OnTime
On time
Early
Totals 3 2 1

When/if you break this report by month or week, this layout has
better visual impact than just a 1column dump. You might notice a
pattern of Lates early in each week, or certain times of the
month... yadayada. Just a suggestion.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your life."


"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:E91A8B20-6A03-42CF-BC94-94D14A294304@xxxxxxxxxxxxxxxx
Is there any way to summarize the total of Early, On Time and
Late on the query or report?
Thanks again!
John


"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:B1171988-68AA-447B-9AB8-48A8E9C9088B@xxxxxxxxxxxxxxxx
WOW.....Thanks for the quick response and it works. I was
amazed!


"Al Campagna" <newsgroups@xxxxxxxxxxx> wrote in message
news:%23RfXlnnbIHA.5164@xxxxxxxxxxxxxxxxxxxxxxx
John,
=
IIf([ShipDate]<=DateAdd("d",-3,[DueDate]),"Early",IIf([ShipDate]>=DateAdd("d",-2,[DueDate])
And [ShipDate]<=[DueDate],"On Time","Late"))

Please check my syntax for "finger checks", but that should do
it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your life."


"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:AE539D76-7D35-4062-8F8B-1CCC160A6AC9@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




















.

Relevant Pages

  • Re: On Time Delivery Query
    ... Microsoft Access MVP ... The totals for each group should be done on the report footers, ... like ShipStatus. ...
    (microsoft.public.access.reports)
  • Re: On Time Delivery Query
    ... Microsoft Access MVP ... and you'll never work a day in your life." ... name like ShipStatus. ...
    (microsoft.public.access.reports)
  • Re: Automatically adding a primary key
    ... Microsoft Access MVP ... and you'll never work a day in your life." ... I am looking to add records to a table with patient information via a ...
    (microsoft.public.access.forms)
  • Re: Refill Combobox for each row
    ... Microsoft Access MVP ... and you'll never work a day in your life." ... The function only seems to refill the list ...
    (microsoft.public.access.formscoding)
Loading