Textbox calculation based on checkbox

[Guest]

I have a Cust table with a TaxEligible check box

In my textbox in my Invoicing form I need something that does this

If tblCust.TaxEligible = yes then
Tax = Subtotal * TaxRate
Else
Tax = 0
end if

Or is there a formula which could do the same thing using the default values
of yes/no

=Subtotal * TaxRate * tblCust.TaxEligible *-1

Also, would "tblCust.TaxEligible" be the proper way to reference that field?

Thanks
~Ryan

 

[Douglas J Steele]

No, you can't refer to values in a field like that.

You'll need to use DLookup to check the value of the field:

If Nz(DLookup("TaxEligible", "tblCust", "CustNo = " & CustNumber), False)
Then
Tax = Subtotal * TaxRate
Else
Tax = 0
End If

This assumes that tblCust has a field CustNo in it that holds the customer
number, you know the number of the customer you're checking for
(CustNumber), and that CustNo is a numeric field. If CustNo is a text field,
you need to use quotes:

If Nz(DLookup("TaxEligible", "tblCust", "CustNo = '" & CustNumber & "'"),
False) Then
Tax = Subtotal * TaxRate
Else
Tax = 0
End If

You could simplify by using the fact that True is -1 and False is 0, but
that won't make much difference in terms of efficiency, and I think the
approach above is easier to understand when you go back to look at your code
months later.

 

[Guest]

I have a field called CustNum which is a text field. In your statement I
change "CustNo = " to "CustNum = ", but I'm not sure what CustNumber is
referencing. Would that be the current customer number being viewed on the
form? Which in my case would be InvCustNum.

Also, would I put that If statement into the control source, or should I put
it into the code builder as an After Update

~Ryan

 

[Douglas J. Steele]

Try putting the code into your form's Current event, so that it calculates
Tax each time you move to a different customer.

Yes, CustNumber in my example was intended to be the number of the customer
being viewed.