Calculated Field

  • Thread starter Thread starter PennyB
  • Start date Start date
P

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
 
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
 
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



Clifford Bass said:
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 said:
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
Click to expand...
Click to expand...
 
I was having a brain freeze - been building this database for way too long.
I got it and thank you - it is all working!!

PennyB said:
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



Clifford Bass said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top