Prev: SQL select statement question
Next: Duplicate Queries
From: clueless on 14 Jan 2010 18:13
I am trying to write an IIf statement with the following info. If the
[StatusOfAppointment]="OnTime" and the [DriverTime] is greater than 2 hours
then "Detention" and then if the [StatusOfAppointment]="Early" then need to
use [DepartureTime]- [AppointmentTime] and if this is greater than 2 hours
"Detention",Null. I'm not sure how to put greater than 2 hours in the
statement. Any help would be greatly appreciated. Thank you in advance!
--
clueless
From: Jeff Boyce on 14 Jan 2010 19:09
Consider approaching this in steps, rather than all at once.

(the following assumes all these fieldnames are collected together into one
query ... or one table)

In a query, you could add a new field that was the difference between
[DepartureTime] and [AppointmentTime]. From your description, if this
difference is 2 hours or less, you don't care about teh record. If this is
true, you could add ">2" in the Selection Criterion area under your new
difference field (this assumes you measure in hours -- if not, format it
until you have hours).

Then it sounds like you only care when the [StatusOfAppointment] = "Early"
.... add that as a selection criterion.

I don't understand about [DriverTime], 2 hours and "Detention".

A selection criterion for [StatusOfAppointment] would be "OnTime".

Does that get you closer (and without having to puzzle out the IIF()
statement?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"clueless" <clueless(a)discussions.microsoft.com> wrote in message
news:75377903-156C-4EA8-A0B6-245CE3573084(a)microsoft.com...
>I am trying to write an IIf statement with the following info. If the
> [StatusOfAppointment]="OnTime" and the [DriverTime] is greater than 2
> hours
> then "Detention" and then if the [StatusOfAppointment]="Early" then need
> to
> use [DepartureTime]- [AppointmentTime] and if this is greater than 2 hours
> "Detention",Null. I'm not sure how to put greater than 2 hours in the
> statement. Any help would be greatly appreciated. Thank you in advance!
> --
> clueless


From: KARL DEWEY on 14 Jan 2010 19:48
If your data is stored in hours then --
[DriverTime] > 2

If it is in minutes then --
[DriverTime] > 120

Otherwise use criteria appropriate to the data.

--
Build a little, test a little.


"clueless" wrote:

> I am trying to write an IIf statement with the following info. If the
> [StatusOfAppointment]="OnTime" and the [DriverTime] is greater than 2 hours
> then "Detention" and then if the [StatusOfAppointment]="Early" then need to
> use [DepartureTime]- [AppointmentTime] and if this is greater than 2 hours
> "Detention",Null. I'm not sure how to put greater than 2 hours in the
> statement. Any help would be greatly appreciated. Thank you in advance!
> --
> clueless
 | 
Pages: 1
Prev: SQL select statement question
Next: Duplicate Queries