Is it possible to use calculations created in unbound text boxs on a form in a query? If so; how?
Is it possible to use calculations created in unbound text boxs on a form in a query? If so; how?
Forms!FormName!TextBoxName
I'm sorry I am not very good with this...where do I put that?
SELECT [BILLING/COMMISSION].Client, [Sales Reps].[Sales Rep], [BILLING/COMMISSION].[Trade/Cash], [BILLING/COMMISSION].[Commission Type], [BILLING/COMMISSION].[Billing Month], [BILLING/COMMISSION].[Monthly Billing Total]
FROM [Sales Reps] INNER JOIN [BILLING/COMMISSION] ON [Sales Reps].[Sales Rep ID] = [BILLING/COMMISSION].[Sales Rep];
I want to add "Commission owed" from "BILLING/COMMISSION"
Why don't you do the same calculation that is on your form here in the query instead?
SELECT [BILLING/COMMISSION].Client, [Sales Reps].[Sales Rep], [BILLING/COMMISSION].[Trade/Cash], [BILLING/COMMISSION].[Commission Type], [BILLING/COMMISSION].[Billing Month], [BILLING/COMMISSION].[Monthly Billing Total], Format(Forms!Formname!Textboxname, Currency) AS "Commission Owed"
FROM [Sales Reps] INNER JOIN [BILLING/COMMISSION] ON [Sales Reps].[Sales Rep ID] = [BILLING/COMMISSION].[Sales Rep];
Can I do that?
here is the calculation....
=IIf([Payment_Received]=Yes And [Commission Type]=1,[Monthly Billing Total]*0.1,IIf([Payment_Received]=Yes And [Commission Type]=2,[Monthly Billing Total]*0.05,IIf([Payment_Received]=Yes And [Commission Type]=3,[Monthly Billing Total]*0.02,IIf([Payment_Received]=Yes And [Commission Type]=4,0,0))))
Perfect! Copy it directly as is to a field in the query, remove the first "=" and give it a name:
Commission Owed: IIf......
You might want to make it look pretty, use the Format function, an example:
Commission Owed: Format(IIf......,Currency)