Change output based on value of another field

[TraciAnnNeedsHelp]

Using a Query:

When the value of one field (TaskID) is 2 or 6 AND the value of another
field (Hourly) is less than 4 the Hourly field needs changed to 4 (or the
current value if >4).

Likewise, when the value of (TaskID) is 2 or 6 (Amount) needs changed to a
different value.

To make it even more complicated; when (TaskID) is 3 or 7 the minimum value
of (Hourly) changes to 2; and the (Amount) changes too.

Thanx for your help!
TraciAnn

 

[John W. Vinson]

Using a Query:

When the value of one field (TaskID) is 2 or 6 AND the value of another
field (Hourly) is less than 4 the Hourly field needs changed to 4 (or the
current value if >4).

Likewise, when the value of (TaskID) is 2 or 6 (Amount) needs changed to a
different value.

To make it even more complicated; when (TaskID) is 3 or 7 the minimum value
of (Hourly) changes to 2; and the (Amount) changes too.

Thanx for your help!
TraciAnn

Do you want to permanently change the value in the table? or just change what
you're displaying? Is this something you need to do once-off, or will it be an
ongoing process?

 

[TraciAnnNeedsHelp]

Thanks for your reply.

I do not want to change the value in the table, only in the query. I need to
run this daily.

 

[John Spencer]

Your description does not help.

1) TaskID = 2 or 6 and Hourly <4 then change hourly to 4

2) TaskID = 2 or 6 Change Amount (to what)

3) TaskID = 3 or 7 change Hourly to 2 (or 1 or 0) and change Amount to what?

Are you trying to change the values permanently or just calculate the
values in a query? You want to change two different values which means
two calculations.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[TraciAnnNeedsHelp]

1) TaskID = 2 or 6 and Hourly <4 then change hourly to 4

2) TaskID = 2 or 6 Change Amount to 125

3) TaskID = 3 or 7 and Hourly <2 then change hourly to 2

4) TaskID = 3 or 7 Change Amount to 95

I hope that's better. Also, looking at the data I noticed that some of my
records have a zero value for Hourly. When this is the case, the value should
not change. So in essence, when Hourly is greater than zero but less than the
minimum (2 or 4).

I hope that better explains it.
Thanks for your help!
TraciAnn

 

[John Spencer]

IIF(TaskId in (2,6),125,IIF(TaskID in (3,7),95,Amount))

IIF(TaskId in (2,6) and Hourly<4 and Hourly >0,4,
IIF(TaskID in (3,7) and Hourly<2 and Hourly >0,2,Hourly))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[TraciAnnNeedsHelp]

Thank you for the formulas. I'm uncertain what to do with them. Do I place
these as expressions within their respective fields?

 

[John Spencer]

They are separate calculated fields and each formula goes in a field
"box" in the query. If you want a title other than the automatic EXPR#
then you preface the formula with that title. Something like:

FIELD: TheAmount: IIF(TaskId in (2,6),125,IIF(TaskID in (3,7),95,Amount))


FIELD: TheRate: IIF(TaskId in (2,6) and Hourly<4 and Hourly >0,4,
IIF(TaskID in (3,7) and Hourly<2 and Hourly >0,2,Hourly))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[TraciAnnNeedsHelp]

John, THANK YOU!!! It worked like a charm.

They are separate calculated fields and each formula goes in a field
"box" in the query. If you want a title other than the automatic EXPR#
then you preface the formula with that title. Something like:

FIELD: TheAmount: IIF(TaskId in (2,6),125,IIF(TaskID in (3,7),95,Amount))


FIELD: TheRate: IIF(TaskId in (2,6) and Hourly<4 and Hourly >0,4,
IIF(TaskID in (3,7) and Hourly<2 and Hourly >0,2,Hourly))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================