CODEWITHSUNDEEP
ms-access
FishFold
FishFold

Reputation: 51

MS Access: If/else condition to display data in a Form

I have created a form that allows a user to select a Company and input an Amount.
e.g.
Company Name | Taxable
ABC | YES
BCD | YES
CDE | NO
DEF | NO
...... and the list goes on

Currently, the form will display 2 calculated amounts for each company (YES and NO), with and without tax. How and where do I insert a condition such that my Form only displays the Amount with tax for YES and Amount without tax for NO?

EDIT 1:
YES and NO values are stored as text.

My form is a based on a query that gets data from multiple tables. There is a Product table that actually calculates and stores both Taxed and Untaxed values using different formulas. The form will display these 2 fields as the Taxed and Untaxed values.

EDIT 2:

My SELECT statement for the query which joins the tables to create my form

Initial:
SELECT [Refund].[Request Date], [Refund].[Adj Date], [Refund].[Company ID], Company.[Company Name], Company.[Taxable], [Refund].[Product Name], [Product].Stake, [Product].[Sales Comm Amount], [Product].[GST on SC], [Product].[TaxedAmount], [Product].[UntaxedAmount] FROM Company INNER JOIN ([Product] INNER JOIN [Refund] ON [Product].[Product Name] = [Refund].[Product Name]) ON Company.[Company ID] = [Refund].[Company ID];

Tried with new line:
SELECT [Refund].[Request Date], [Refund].[Adj Date], [Refund].[Company ID], Company.[Company Name], Company.[Taxable], [Refund].[Product Name], [Product].Stake, [Product].[Sales Comm Amount], [Product].[GST on SC], [Product].[TaxedAmount], [Product].[UntaxedAmount], IIF([Taxable]="YES", [TaxedAmount], [UntaxedAmount]) AS FinalAmount FROM Company INNER JOIN ([Product] INNER JOIN [Refund] ON [Product].[Product Name] = [Refund].[Product Name]) ON Company.[Company ID] = [Refund].[Company ID];

Also created an empty column in my [Refund] table called FinalAmount hoping that the IIF function would store a value at the end. However, I was met with the message "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." when trying to save in SQL view.

Upvotes: 0

Views: 3010

Answers (1)

Gustav
Gustav

Reputation: 55921

Use a ControlSource like this for your textbox that displays the amount:

=IIf([Taxable],[AmountWithTax],[AmountWithoutTax])

If/when Taxable is stored as text (it really shouldn't):

=IIf([Taxable]="YES",[AmountWithTax],[AmountWithoutTax])

or, if the separator is localised:

=IIf([Taxable]="YES";[AmountWithTax];[AmountWithoutTax])

For use in a query:

Select *, IIf([Taxable]="YES",[AmountWithTax],[AmountWithoutTax]) As TaxedAmount
From YourTable

Upvotes: 1

Related Questions