Results 1 to 6 of 6
  1. #1
    bkirsch is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2011
    Posts
    39

    Form calculations in query

    Is it possible to use calculations created in unbound text boxs on a form in a query? If so; how?

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Forms!FormName!TextBoxName

  3. #3
    bkirsch is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2011
    Posts
    39
    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"

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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];

  5. #5
    bkirsch is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2011
    Posts
    39
    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))))

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Parameter Query Calculations
    By mrk7891 in forum Queries
    Replies: 2
    Last Post: 11-04-2011, 04:33 PM
  2. Replies: 7
    Last Post: 09-12-2011, 12:03 PM
  3. Calculations in a form
    By elmere in forum Forms
    Replies: 3
    Last Post: 04-27-2011, 04:11 PM
  4. Calculations in Query
    By jdhaldane in forum Queries
    Replies: 5
    Last Post: 12-10-2010, 05:57 AM
  5. MSACCESS Query with calculations?
    By Masterfinn in forum Queries
    Replies: 10
    Last Post: 02-24-2010, 10:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums