Date Calculation (1 Viewer)

[scgoodman]

Trying to do a date calculation. I have two dates, Delivery Date and Due Date. I want to show whether it is ontime.

Date format is MM/DD/YY 6:34PM for both.

I wrote this statement:
IIf([Due Date]<=[Delivery Date],'No',IIf([Due Date]>[Delivery Date],'Yes'))

This works, however if the due date falls on the same date as the due date it shows no. Even though something was delivered at 02/27/08 10:20AM and it wasn't due until 02/27/08 12:00PM it shows N. Any help.

 

[Rabbie]

Perhaps

IIf([Due Date]<[Delivery Date],'No','Yes'))

would do what you want.

 

[MSAccessRookie]

Trying to do a date calculation. I have two dates, Delivery Date and Due Date. I want to show whether it is ontime.

Date format is MM/DD/YY 6:34PM for both.

I wrote this statement:
IIf([Due Date]<=[Delivery Date],'No',IIf([Due Date]>[Delivery Date],'Yes'))

This works, however if the due date falls on the same date as the due date it shows no. Even though something was delivered at 02/27/08 10:20AM and it wasn't due until 02/27/08 12:00PM it shows N. Any help.

1. The Condition Part of the first IIf Statement is [Due Date] <=[Delivery Date]. This makes the Due Date responds 'No'. You needs to have [Due Date] < [Delivery Date] instead.
2. Since you only have two possibilities ('Yes' and 'No'), then a compound IIf Statement is not required.

The corrected IIf Statement would be:

IIf(([Due Date] < [Delivery Date]), 'No', 'Yes')

 

[scgoodman]

Tried that...but have a record that is showing no and dates and times are...

Delivery Date = 2/3/2009 7:10:00 AM
Due Date = 2/3/2009 4:30:00 PM

however shows yes if on a different date, or is actually late.

Thoughts

 

[MSAccessRookie]

Tried that...but have a record that is showing no and dates and times are...

Delivery Date = 2/3/2009 7:10:00 AM
Due Date = 2/3/2009 4:30:00 PM

however shows yes if on a different date, or is actually late.

Thoughts

Your date includes the time part as well, so by your standards, that delivery was over 8 Hours late. If you just want to compare the date, then the Datediff() function using the "d" option might be what you are looking for.

IIf(Datediff("d", [Due Date], [Delivery Date]) >= 0 , 'Yes', 'No')

 

[boblarson]

Or using Rabbie's IIF, but because you have time included we go with

IIf(DateValue([Due Date])<DateValue([Delivery Date]),'No','Yes'))

 

[raskew]

Your date includes the time part as well, so by your standards, that delivery was over 8 Hours late.

???

From the debug window:

DeliveryDate = #2/3/2009 7:10:00 AM# 
DueDate = #2/3/2009 4:30:00 PM#
OnTime = Iif(DueDate<DeliveryDate, "no", "yes")
? Ontime
yes

? datediff("n", DeliveryDate, DueDate)
 560 
i.e. delivered 9 hours 20 minutes before the due date

Help me here, I'm so confused.

Bob

 

[MSAccessRookie]

???

From the debug window:

DeliveryDate = #2/3/2009 7:10:00 AM# 
DueDate = #2/3/2009 4:30:00 PM#
OnTime = Iif(DueDate<DeliveryDate, "no", "yes")
? Ontime
yes
 
? datediff("n", DeliveryDate, DueDate)
 560 
i.e. delivered 9 hours 20 minutes before the due date

Help me here, I'm so confused.

Bob

If a delivery is due at 8:00 in the morning, and it does not arrive until after 4:00 in the afternoon, then when you consider both the time and the date, it is 8 hours late, even though it occurs on the same day. What BobLarson and I have suggested provides two ways to compare the date only, which would provide a match in that case.

 

[raskew]

DueDate = #2/3/2009 4:30:00 PM# that means that it's due at 1630 in the afternoon - not 8:00 AM in the morning.

Are we reading from two different sheets?

Bob

 

[raskew]

Tried that...but have a record that is showing no and dates and times are...

Delivery Date = 2/3/2009 7:10:00 AM
Due Date = 2/3/2009 4:30:00 PM

however shows yes if on a different date, or is actually late.

If this is the case, there's something else going on that we're not seeing. Changing the dates (e.g. due time = 0800) is probably not the solution.

Maybe Rookie can help us out here.

Bob