complicated expression

[d.p.]

I need to create a conditional expression that's not a simple one.
I need the expression to be in the field of a table, that depends on another
field (different column) in that same table. It should be something like
this:

ColumnA
-----------
valueA = X
valueB = Y
valueC = Z

ColumnB
----------
value1 = 30% of valueA, between 3000 and 100,000
value2 = 30% of valueB, between 3000 and 100,000
value3 = 30% of valueC, between 3000 and 100,000

If value1, 2 or 3 is less than 3,000 it stays at 3,000; if it's more than
100,000 it stays at 100k. Anything in between remains 30%.

Thanks!

[Yannick Turgeon]

SELECT ColumnA, IIf(ColumnA * .3 < 3000, 3000, IIf(ColumnA * .3 > 100000,
100000, ColumnA * .3)) AS ColumnB
FROM YourTable

I hope I understood correctly what you want.

Yannick

"d.p." <d_*****@yahoo.com> wrote in message
news:40**********************@news.zen.co.uk...

I need to create a conditional expression that's not a simple one.
I need the expression to be in the field of a table, that depends on another field (different column) in that same table. It should be something like
this:

ColumnA
-----------
valueA = X
valueB = Y
valueC = Z

ColumnB
----------
value1 = 30% of valueA, between 3000 and 100,000
value2 = 30% of valueB, between 3000 and 100,000
value3 = 30% of valueC, between 3000 and 100,000

If value1, 2 or 3 is less than 3,000 it stays at 3,000; if it's more than
100,000 it stays at 100k. Anything in between remains 30%.

Thanks!

[d.p.]

thanks, works great! I had the feeling it would be that but it only works
within a query. Is there a way to have this in the table itself?
It's like with Excel, a value in column B is a formula that depends on the
value of a cell in column A.
"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:us********************@news20.bellglobal.com. ..

SELECT ColumnA, IIf(ColumnA * .3 < 3000, 3000, IIf(ColumnA * .3 > 100000,
100000, ColumnA * .3)) AS ColumnB
FROM YourTable

I hope I understood correctly what you want.

Yannick

"d.p." <d_*****@yahoo.com> wrote in message
news:40**********************@news.zen.co.uk...

I need to create a conditional expression that's not a simple one.
I need the expression to be in the field of a table, that depends on

another

field (different column) in that same table. It should be something like
this:

ColumnA
-----------
valueA = X
valueB = Y
valueC = Z

ColumnB
----------
value1 = 30% of valueA, between 3000 and 100,000
value2 = 30% of valueB, between 3000 and 100,000
value3 = 30% of valueC, between 3000 and 100,000

If value1, 2 or 3 is less than 3,000 it stays at 3,000; if it's more than 100,000 it stays at 100k. Anything in between remains 30%.

Thanks!

[Yannick Turgeon]

No, you can't.

Even if I don't know anything about your precise goal, I'm sure a query will
do the work you want. Just save this SQL code I gave you as a query, open it
directly - from Access or programmaticaly - and you will be able to change
ColumnA and see the change in ColumnB in your query. This will be exactly as
it would be a table with a calculated field.

HTH

Yannick

"d.p." <d_*****@yahoo.com> wrote in message
news:40**********************@news.zen.co.uk...

thanks, works great! I had the feeling it would be that but it only works
within a query. Is there a way to have this in the table itself?
It's like with Excel, a value in column B is a formula that depends on the
value of a cell in column A.
"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:us********************@news20.bellglobal.com. ..

SELECT ColumnA, IIf(ColumnA * .3 < 3000, 3000, IIf(ColumnA * .3 > 100000,

100000, ColumnA * .3)) AS ColumnB
FROM YourTable

I hope I understood correctly what you want.

Yannick

"d.p." <d_*****@yahoo.com> wrote in message
news:40**********************@news.zen.co.uk...

I need to create a conditional expression that's not a simple one.
I need the expression to be in the field of a table, that depends on

another

field (different column) in that same table. It should be something like this:

ColumnA
-----------
valueA = X
valueB = Y
valueC = Z

ColumnB
----------
value1 = 30% of valueA, between 3000 and 100,000
value2 = 30% of valueB, between 3000 and 100,000
value3 = 30% of valueC, between 3000 and 100,000

If value1, 2 or 3 is less than 3,000 it stays at 3,000; if it's more

than 100,000 it stays at 100k. Anything in between remains 30%.

Thanks!

[d.p.]

ok, thanks a lot for your help! much appretiated
"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:Hc********************@news20.bellglobal.com. ..

No, you can't.

Even if I don't know anything about your precise goal, I'm sure a query will do the work you want. Just save this SQL code I gave you as a query, open it directly - from Access or programmaticaly - and you will be able to change
ColumnA and see the change in ColumnB in your query. This will be exactly as it would be a table with a calculated field.

HTH

Yannick

"d.p." <d_*****@yahoo.com> wrote in message
news:40**********************@news.zen.co.uk...

thanks, works great! I had the feeling it would be that but it only works

within a query. Is there a way to have this in the table itself?
It's like with Excel, a value in column B is a formula that depends on the value of a cell in column A.
"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:us********************@news20.bellglobal.com. ..

SELECT ColumnA, IIf(ColumnA * .3 < 3000, 3000, IIf(ColumnA * .3 >

100000, 100000, ColumnA * .3)) AS ColumnB
FROM YourTable

I hope I understood correctly what you want.

Yannick

"d.p." <d_*****@yahoo.com> wrote in message
news:40**********************@news.zen.co.uk...
> I need to create a conditional expression that's not a simple one.
> I need the expression to be in the field of a table, that depends on
another
> field (different column) in that same table. It should be something like > this:
>
> ColumnA
> -----------
> valueA = X
> valueB = Y
> valueC = Z
>
> ColumnB
> ----------
> value1 = 30% of valueA, between 3000 and 100,000
> value2 = 30% of valueB, between 3000 and 100,000
> value3 = 30% of valueC, between 3000 and 100,000
>
> If value1, 2 or 3 is less than 3,000 it stays at 3,000; if it's more

than

> 100,000 it stays at 100k. Anything in between remains 30%.
>
> Thanks!
>
>