Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    7

    Unanswered: Totaling amount dependent on value in a seperate combo box

    In my database I have several fields that are to contain a dollar amount and then a combo box associated with that dollar field to select where the funds are coming from. Example:

    Text Box for dollar amount 1 - combo box 1 (options 1, 2, or 3)
    Text box for dollar amount 2 - combo box 2 (options 1, 2, or 3)
    Text box for dollar amount 3 - combo box 3 (options 1, 2, or 3)

    I have many of these in my database and there are more than 3 options in the combo box and I may be adding more in the future. What I want to be able to do is total the text boxes where the associated combo box is option 1. And then total all the text boxes where the associated combo box is option 2. Etc. Is there a way to easily do this? Or even do this at all? Any help is appreciated.

    Edited to add: I'm using Access 2007.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    Could you clarify something for me? When you say:
    Quote Originally Posted by theresat
    In my database I have several fields that are to contain a dollar amount and then a combo box associated with that dollar field to select where the funds are coming from. Example:

    Text Box for dollar amount 1 - combo box 1 (options 1, 2, or 3)
    Text box for dollar amount 2 - combo box 2 (options 1, 2, or 3)
    Text box for dollar amount 3 - combo box 3 (options 1, 2, or 3)

    I have many of these in my database and there are more than 3 options in the combo box and I may be adding more in the future.
    Does that mean you have multiple text boxes and multiple combo boxes for a single record, or that you have one of each per record? Could you also include the name(s) of the control(s)? Thanks!
    Me.Geek = True

  3. #3
    Join Date
    Jun 2009
    Posts
    7
    First of all, Thank you so much for helping, I really appreciate it. Now to answer your questions.

    I have multiple text boxes and multiple combo boxes for each single record. I won't give all the names of the controls because there are so many, but here are some of them:

    Text Boxes - Combo Boxes
    WX Amount - WX Funds Source
    WX Amount 2 - WX Funds Source 2
    WX Amount 3 - WX Funds Source 3
    Pre Amount - Pre Fund Source
    Post Amount - Post Fund Source
    CT Amount - CT Funds Source
    Fridge Amount - Fridge Funding Source
    Fridge Amount 2 - Fridge Funding Source 2

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    First off, I'd recommend you name your controls without spaces

    The best way I see to do it is to simply code them in, like:
    Code:
    Dim curOpt1 as currency
    Dim curOpt2 as currency
    Dim curOpt3 as currency
    
    curOpt1 = 0
    curOpt2 = 0
    curOpt3 = 0
    
    If me.WXFundsSource = 'Option 1' then
        curOpt1 = curOpt1 + ccur(nz(me.WXAmount,0))
    elseif me.WXFundsSource = 'Option 2' then
        curOpt2 = curOpt2 + ccur(nz(me.WXAmount,0))
    elseif me.WXFundsSource = 'Option 3' then
        curOpt3 = curOpt3 + ccur(nz(me.WXAmount,0))
    endif
    
    'repeat as necessary
    (untested code)

    It might be possible to loop through the controls checking for the tag property or something, but that would take a fair amount of coding and I think you'd probably save yourself time in the longrun if you just code them in.
    Me.Geek = True

  5. #5
    Join Date
    Jun 2009
    Posts
    7
    Thanks! Where do I put this code? Can I do this right in the query or is it something that needs to be done in a form?

    Quote Originally Posted by nckdryr
    First off, I'd recommend you name your controls without spaces

    The best way I see to do it is to simply code them in, like:
    Code:
    Dim curOpt1 as currency
    Dim curOpt2 as currency
    Dim curOpt3 as currency
    
    curOpt1 = 0
    curOpt2 = 0
    curOpt3 = 0
    
    If me.WXFundsSource = 'Option 1' then
        curOpt1 = curOpt1 + ccur(nz(me.WXAmount,0))
    elseif me.WXFundsSource = 'Option 2' then
        curOpt2 = curOpt2 + ccur(nz(me.WXAmount,0))
    elseif me.WXFundsSource = 'Option 3' then
        curOpt3 = curOpt3 + ccur(nz(me.WXAmount,0))
    endif
    
    'repeat as necessary
    (untested code)

    It might be possible to loop through the controls checking for the tag property or something, but that would take a fair amount of coding and I think you'd probably save yourself time in the longrun if you just code them in.

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Well, what are you planning to use it for? If it's for a query, then you're going to have to build an SQL statement analagous to the above VBA code. What I gave you is code for a form (since you gave me controls like textboxes and comboboxes, not field names in a table).
    Me.Geek = True

  7. #7
    Join Date
    Jun 2009
    Posts
    7
    I had given the form controls because that's how the data is entered. I want to create a report, so it would be for a query.

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Then I would be adding together a bunch of IIF statements, something like:

    Code:
    SELECT
           (IIF(tblName.[WX Funds Source] = 'Option 1',[tblName].[WX Amount],0) + IIF(tblName.[WX Funds Source 2] = 'Option 1',[tblName].[WXAmount2],0)) AS Sum1
    Repeat as necessary
    Me.Geek = True

  9. #9
    Join Date
    Jun 2009
    Posts
    7
    Thanks! That's working out great for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •