Prev: Convert seconds to hours
Next: Query Question...
From: Cam on 6 Apr 2010 17:46
Hello,

I am trying to do a query where I need a result based on 3 criteria, but not
sure how to do it. in written term would be like this for a new fields
(column in query).
1) If OprStat = 1 and StartDate < today date, return "LateN"
2) If OprStat = 3 and StartDate < today date, return "LateM"
3) If does not fall into (1) & (2) condition, return "OnTime"

Sample Data:
Order OprStat StartDate
1110 1 4/1/2010
1111 1 4/12/2010
1112 3 4/2/2010

Results: assuming current date is 4/6/2010.
Order OprStat StartDate Result
1110 1 4/1/2010 LateN
1111 1 4/12/2010 OnTime
1112 3 4/2/2010 LateM
From: KARL DEWEY on 6 Apr 2010 18:05
Try this --
IIF([OprStat] = 1 and [StartDate] < Date(), "LateN", IIF([OprStat] = 3 and
[StartDate] < Date(), "LateM", "OnTime"))

--
Build a little, test a little.


"Cam" wrote:

> Hello,
>
> I am trying to do a query where I need a result based on 3 criteria, but not
> sure how to do it. in written term would be like this for a new fields
> (column in query).
> 1) If OprStat = 1 and StartDate < today date, return "LateN"
> 2) If OprStat = 3 and StartDate < today date, return "LateM"
> 3) If does not fall into (1) & (2) condition, return "OnTime"
>
> Sample Data:
> Order OprStat StartDate
> 1110 1 4/1/2010
> 1111 1 4/12/2010
> 1112 3 4/2/2010
>
> Results: assuming current date is 4/6/2010.
> Order OprStat StartDate Result
> 1110 1 4/1/2010 LateN
> 1111 1 4/12/2010 OnTime
> 1112 3 4/2/2010 LateM
From: RedHeadedMonster via AccessMonster.com on 6 Apr 2010 18:16
IIF ([startdate]>Now(), "OnTime", (IIF ([OprStat] = 1, "LateN', (IIF ([OpStat]
=3, "LateM", "")))))

That might do it.
RHM

Cam wrote:
>Hello,
>
>I am trying to do a query where I need a result based on 3 criteria, but not
>sure how to do it. in written term would be like this for a new fields
>(column in query).
>1) If OprStat = 1 and StartDate < today date, return "LateN"
>2) If OprStat = 3 and StartDate < today date, return "LateM"
>3) If does not fall into (1) & (2) condition, return "OnTime"
>
>Sample Data:
>Order OprStat StartDate
>1110 1 4/1/2010
>1111 1 4/12/2010
>1112 3 4/2/2010
>
>Results: assuming current date is 4/6/2010.
>Order OprStat StartDate Result
>1110 1 4/1/2010 LateN
>1111 1 4/12/2010 OnTime
>1112 3 4/2/2010 LateM

--
Message posted via http://www.accessmonster.com

From: david on 7 Apr 2010 07:35
SWITCH
(OprStat = 1 and StartDate < today date), "LateN",
(If OprStat = 3 and StartDate < today date), "LateM",
True, "OnTime"


For multiple choices, SWITCH may be clearer than nested IIFs

(david)


"Cam" <Cam(a)discussions.microsoft.com> wrote in message
news:7AF42988-694D-44E0-A0DB-4D56E31345DE(a)microsoft.com...
> Hello,
>
> I am trying to do a query where I need a result based on 3 criteria, but
> not
> sure how to do it. in written term would be like this for a new fields
> (column in query).
> 1) If OprStat = 1 and StartDate < today date, return "LateN"
> 2) If OprStat = 3 and StartDate < today date, return "LateM"
> 3) If does not fall into (1) & (2) condition, return "OnTime"
>
> Sample Data:
> Order OprStat StartDate
> 1110 1 4/1/2010
> 1111 1 4/12/2010
> 1112 3 4/2/2010
>
> Results: assuming current date is 4/6/2010.
> Order OprStat StartDate Result
> 1110 1 4/1/2010 LateN
> 1111 1 4/12/2010 OnTime
> 1112 3 4/2/2010 LateM


 | 
Pages: 1
Prev: Convert seconds to hours
Next: Query Question...