Help with IIf statement

[clueless]

I posted this the other day. Someone did respond, but when I click on the
link it is blank and I searched all over and could not find my question or
the response. I thank who ever answered it and hopefully you can respond
again.

My IIf statement is as follows:
StatusOfAppointment: IIf([ArrivalTime]<[AppointmentTime],"Early",
IIf[ArrivalTime]=[AppointmentTime],"On Time","Late"))

I need to give a 15 minute window (ex. appointment time 15:00, driver would
be consider On Time from 15:00 to 15:15). Not sure if I can do that, so any
help would truly be appreciated. Thanks!

 

[fredg]

I posted this the other day. Someone did respond, but when I click on the
link it is blank and I searched all over and could not find my question or
the response. I thank who ever answered it and hopefully you can respond
again.

My IIf statement is as follows:
StatusOfAppointment: IIf([ArrivalTime]<[AppointmentTime],"Early",
IIf[ArrivalTime]=[AppointmentTime],"On Time","Late"))

I need to give a 15 minute window (ex. appointment time 15:00, driver would
be consider On Time from 15:00 to 15:15). Not sure if I can do that, so any
help would truly be appreciated. Thanks!

This was my reply yesterday.

So is that 3:00 AM or 3:00 PM?
Is it considered on time if the Arrival time for an Appointment for
3:00 PM is actually 3:10 AM the next morning? That would meet your
criteria.

You're also missing an ending quote after the word Late in your
expression.

StatusOfAppointment:IIf([ArrivalTime]<[AppointmentTime],"Early",
IIf([ArrivalTime] Between [AppointmentTime] and
DateAdd("n",15,[AppointmentTime]),"On Time","Late"))

 

[Ken Sheridan]

How are you entering the times? If you are simply entering the time of day
into a date/time data type column then what you are in fact entering is the
time of day on 30 December 1899, which is day-zero in Access's date/time
implementation. This will work provided the arrival and appointment times
are both in the same day, but if the times span midnight then the 'later'
arrival time is in fact earlier than the appointment time.

To cater for this scenario you'd need to include the actual date in both
columns. e.g. #07/27/2008 23:55:00# as the appointment time and #07/28/2008
00:10:00# as the arrival time if 15 minutes later.

I think the expression in your original post must have differed slightly
from that in this thread to judge by Fred's reply (in this one the final
quotes character is there, but the opening parenthesis after the second IIf
is missing), but the expression he gave you should do the trick, subject to
the above caveat.

A more generic solution, allowing different on-time windows to be
stipulated, would be the following function:

Public Function GetStatusOfAppointment(dtmAppointmentTime As Date, _
dtmArrivalTime As Date, _
intWindowMinutes As Integer) As String

Dim intMinuteDiff As Integer

intMinuteDiff = DateDiff("n", dtmAppointmentTime, dtmArrivalTime)

Select Case intMinuteDiff
Case Is < 0
GetStatusOfAppointment = "Early"
Case Is <= intWindowMinutes
GetStatusOfAppointment = "On Time"
Case Else
GetStatusOfAppointment = "Late"
End Select

End Function

Paste the above into a standard module. For a 15 minute window you'd call
it in a query like so:

StatusOfAppointment:GetStatusOfAppointment([AppointmentTime],[ArrivalTime],15)

Ken Sheridan
Stafford, England