I have a subform,(named 'Fldetail'), in which I would like to calculate a sales tax based on the sales price * a tax rate stored in a separate Form. This form is called 'Values'
Just for clarity, forms do not "store" data. Tables store data; forms are a way to view the data stored in tables.
"Values" is a reserved word in Access and shouldn't be used as an object name. A better name would be "frmValues".
Is it possible for the tax rate to change?
If yes, I would save the tax rate in the record. Otherwise, next year, if you look back at a record, the calculated value will be different if the tax rate was changed.
Where are you doing the calculation?
In a query? (design view or SQL view?)
In the control source of a control?
In VBA code?
Are "S" and "TaxCode" boolean (T/F) fields?
If you are in the subform, "S" and "Amount" are available in the sub form, "TaxCode" is on the main form (ie parent form) and the "Tax rate" is on a different form.
NOTE: The form "Values" MUST be open for the calculation to work correctly.
Code:
Iif(s=true and tax code = true, tax rate * amount) + amount
The syntax for the immediate if function (IIF() ) is "IIF(condition, Value if True, Value If False)"
You are missing the FALSE part.
The condition clause is:
Code:
S=TRUE and Forms!Florist.[tax code] = TRUE
The TRUE clause is:
Code:
(Forms!Values.[tax rate] * amount) + amount
The FALSE clause is: 0
Putting it together:
Code:
IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0)
If you were in the query design view, you might use
Code:
ExtendedAmount: IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0)
In SQL view, you might use:
Code:
IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0) AS ExtendedAmount
In the Control Source property of a control:
Code:
= IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0)
If the form "Values" is not open, you might try using the DLOOKUP() function to get the tax rate.