If statement in field form

[Andrew Tatum]

I'm trying to come up with a pretty advanced (I guess) field in a form.
Basically, I want it to check for something before giving a total.
Here's the outline:

IF [MsgLeft] > 1 THEN [MoneyOwed] = 0.00
ELSE
IF [MsgLeft] < 1 THEN [MoneyOwed] = [TotalBookedAmt]-[MoneyUsed]

Whenever I try to do that as a Control Source and then paste it into
Expression Builder it just goes blank. It's like Access doesn't even
accept that. So, I'm thinking that I'm doing something wrong.

Any suggestions?

 

[Guest]

Try this in the [MoneyOwed] field control source

=IIF([MsgLeft] > 1 ,0 ,[TotalBookedAmt]-[MoneyUsed])

 

[Andrew Tatum]

Awesome! Thank you very much. I believe you helped me previously as
well...

I just have one smaller question... whenever I try this:

=([SumBooked]+[SumBonus])-[SumSent]

AND SumBonus is empty... the field that contains the equation is blank.
I tried giving SumBonus a default... but that doesn't do anything. Any
suggestions?

 

[Andrew Tatum]

Never mind, I used your query and made this:

=IIf([SumBonus] Is
Null,[SumBooked]-[SumSent],([SumBooked]+[SumBonus])-[SumSent])

 

[Douglas J Steele]

Safer might be

=(Nz([SumBooked],0)+Nz([SumBonus],0)-Nz([SumSent],0))

just in case either of the other fields are ever Null.

 

[Andrew Tatum]

Amazing.

Let me ask you. Do you have a book for reference or is this just off
the top of your head from experience?

 

[Douglas J Steele]

That one's from the top of my head. Nz's a very commonly used function.