Calculated Field

[PennyB]

Any help would be appreciated. I have a unbound text box that I need to
calculate off several different fields. Here are the parameters:

If [FeeNumber]=2

Then [valuation]*.01

If [valuation]*.01 is less than 100 then it needs to equal 100 and

If [valuation]*.01 is greater than [unit]*[AmountPerUnit] then it should
equal [unit] * [AmountPerUnit] if not then it equals [valuation] * .01

If [Fee Number] = anything but 1 then it should be [AmountPerUnit]*[Unit]

Hope this makes sense. Again any help would be very appreciated. I have
been working on this for a few days.

Thanks,

PennyB

 

[Clifford Bass]

Hi Penny,

I am not able to follow that. Instead of using "it", could you use the
actual field or control name as appropriate? Also, in one place you say "If
[FeeNumber]=2" and in another you say "If [Fee Number] = anything but 1".
These seem to be contradictory. Likewise with the "If [valuation]*.01..."
items. If you were to write it out using the "If xxx Then yyy Else zzz End
If" format, you can then convert it into a nested set of "IIf(xxx, yyy, zzz)"
functions, which would be usable as the control source for your text box.

If [FeeNumber] = 2 Then
??? = [valuation] * .01
If ??? < 100 Then
it = 100
????
Else
????
End If
Else
If [FeeNumber] <> 1 Then
it = [AmountPerUnit] * [Unit]
????
Else
????
End If
End If

Clifford Bass

 

[PennyB]

It - is titled AmountDue and it is an ubound text box. Also, yes I had a
typo it should be if FeeNumber anything but 2. Where do I put the code for
the nested if statements?

Here is what I was doing using a query, which works great in the query, but
I don't know how to get it into the form:

IIf([FeeNumber]=2,[Valuation]*0.01,[NumberofUnits]*[AmountPerUnit])

IIf[FeeNumber]=2 And [Valuation]*0.01<100,100,0)

IIf[FeeNumber]=2 And
[Valuation]*0.01>[NumberofUnits]*[AmountPerUnit],[NumberofUnits]*[AmountPerUnit],0)

Thanks,

PennyB

Hi Penny,

I am not able to follow that. Instead of using "it", could you use the
actual field or control name as appropriate? Also, in one place you say "If
[FeeNumber]=2" and in another you say "If [Fee Number] = anything but 1".
These seem to be contradictory. Likewise with the "If [valuation]*.01..."
items. If you were to write it out using the "If xxx Then yyy Else zzz End
If" format, you can then convert it into a nested set of "IIf(xxx, yyy, zzz)"
functions, which would be usable as the control source for your text box.

If [FeeNumber] = 2 Then
??? = [valuation] * .01
If ??? < 100 Then
it = 100
????
Else
????
End If
Else
If [FeeNumber] <> 1 Then
it = [AmountPerUnit] * [Unit]
????
Else
????
End If
End If

Clifford Bass

Any help would be appreciated. I have a unbound text box that I need to
calculate off several different fields. Here are the parameters:

If [FeeNumber]=2

Then [valuation]*.01

If [valuation]*.01 is less than 100 then it needs to equal 100 and

If [valuation]*.01 is greater than [unit]*[AmountPerUnit] then it should
equal [unit] * [AmountPerUnit] if not then it equals [valuation] * .01

If [Fee Number] = anything but 1 then it should be [AmountPerUnit]*[Unit]

Hope this makes sense. Again any help would be very appreciated. I have
been working on this for a few days.

Thanks,

PennyB

 

[PennyB]

I was having a brain freeze - been building this database for way too long.
I got it and thank you - it is all working!!

It - is titled AmountDue and it is an ubound text box. Also, yes I had a
typo it should be if FeeNumber anything but 2. Where do I put the code for
the nested if statements?

Here is what I was doing using a query, which works great in the query, but
I don't know how to get it into the form:

IIf([FeeNumber]=2,[Valuation]*0.01,[NumberofUnits]*[AmountPerUnit])

IIf[FeeNumber]=2 And [Valuation]*0.01<100,100,0)

IIf[FeeNumber]=2 And
[Valuation]*0.01>[NumberofUnits]*[AmountPerUnit],[NumberofUnits]*[AmountPerUnit],0)

Thanks,

PennyB

Hi Penny,

I am not able to follow that. Instead of using "it", could you use the
actual field or control name as appropriate? Also, in one place you say "If
[FeeNumber]=2" and in another you say "If [Fee Number] = anything but 1".
These seem to be contradictory. Likewise with the "If [valuation]*.01..."
items. If you were to write it out using the "If xxx Then yyy Else zzz End
If" format, you can then convert it into a nested set of "IIf(xxx, yyy, zzz)"
functions, which would be usable as the control source for your text box.

If [FeeNumber] = 2 Then
??? = [valuation] * .01
If ??? < 100 Then
it = 100
????
Else
????
End If
Else
If [FeeNumber] <> 1 Then
it = [AmountPerUnit] * [Unit]
????
Else
????
End If
End If

Clifford Bass

Any help would be appreciated. I have a unbound text box that I need to
calculate off several different fields. Here are the parameters:

If [FeeNumber]=2

Then [valuation]*.01

If [valuation]*.01 is less than 100 then it needs to equal 100 and

If [valuation]*.01 is greater than [unit]*[AmountPerUnit] then it should
equal [unit] * [AmountPerUnit] if not then it equals [valuation] * .01

If [Fee Number] = anything but 1 then it should be [AmountPerUnit]*[Unit]

Hope this makes sense. Again any help would be very appreciated. I have
been working on this for a few days.

Thanks,

PennyB

 

[Clifford Bass]

Hi Penny,

Sometimes "talking" it out and having someone ask questions is useful
in getting one's thinking unfrozen. Glad to be able to help you there.

Clifford Bass