If statement in field form

  • Thread starter Thread starter Andrew Tatum
  • Start date Start date
A

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?
 
Try this in the [MoneyOwed] field control source

=IIF([MsgLeft] > 1 ,0 ,[TotalBookedAmt]-[MoneyUsed])
 
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?
 
Never mind, I used your query and made this:

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

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

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

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