Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Nov 2011
    Posts
    103

    Unanswered: Needing Complicated VBA to Sum

    Iím sorry if Iím not supposed to ask the same question twice but I really need some help and Iím running out of time.

    I have BegYears, EndYears, BegAmts, and EndAmts. I need a calculation in VBA that looks in all BegYears and EndYears fields and if the same year exist list the years in Year fields and sum the corresponding amounts and plug them in YearsAmts. Itíll look something like this.

    These fields will be automatically pulled from the table as they are.

    BegYear1 2004 BegAmt1 1504.11
    BegYear2 2005 BegAmt2 1604.39
    BegYear3 2006 BegAmt3 1704.66
    BegYear4 2007 BegAmt4 1804.93
    BegYear5 2008 BegAmt5 1905.21
    EndYear1 2005 EndAmt1 7495.88
    EndYear2 2006 EndAmt2 7995.63
    EndYear3 2007 EndAmt3 8495.34
    EndYear4 2008 EndAmt4 8995.06
    EndYear5 2009 EndAmt5 9494.80

    These fields is where I need the calculations to plug in the results.

    Year1 Year1Amt
    Year2 Year2Amt
    Year3 Year3Amt
    Year4 Year4Amt
    Year5 Year5Amt
    Year6 Year6Amt

    I need Year1 to auto fill with 2004 and Year2 to auto fill with 2005 from above table and Year1Amt to be 1504.11 and Year2Amt to be 9100.27.

    Year2Amt calculation is 1604.39 (BegAmt2) +7495.88 (EndAmt1).

    Can anyone figure this out for me? I need to be able to look at the result data at any given point to create different reports. Iím attaching a sample database that shows kind of how my actual database looks like. This database only has 2 records but my real database has at least 148 records. If it canít be done I would like to know so I wonít waste any more time on this but I figure there has to be a way.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Your database is not normalized. There should only be one column for storing the years and one column for storing the amounts + one additional column indicating whether one row is Begin or End. That way, a rather simple query would yield the results you're looking for.
    Table structure:
    ---------------
    RowId: AutoNumber, PrimaryKey
    YearPart: Integer, Indexed (duplicates OK)
    Amount: Currency
    EndOfYear: Boolean (Yes/No)

    With your schema, what happens when you need to add a supplemental year? Do you change the structure of the table as well as all dependent objects? You don't work with Access (or any RDMS for that matter) as you would with Excel.
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    103
    I totally understand what you're saying. I wouldn't have the table created this way if this wasn't the only way. I'm only working with the fields that were given to me. I don't have a choice in changing the the field itself. We need each field for working with the Feds and they are picky about how they want their data.

    There will be no more supplemental years. I have total of 40 years and no more. So all fields are already created even if they're left blank for certain records.

    Is it not possible to do what I want? I figured I would end up writing a big case statement.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OK, so basically you need a VBA loop looking like:
    Code:
    Dim i As Long
    
    For i = 1 To 40
        If Me.("BegYear" & i).Value = Me.("EndYear" & i).Value Then
            Me.("Year" & i).Value = Me.("BegYear" & i).Value
            Me.("Year" & i & "Amt").Value = Me.("BegAmt" & i).Value + Me.("EndAmt" & i).Value
        End If
    Next i
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Posts
    103
    Yes pretty much. On open of the form or as the fields are entered I need the calculations to work in the background and fill in the YearX fields and YearXAmt fields. So the end results would like this.

    Year1 2004 Year1Amt 1504.11
    Year2 2005 Year2Amt 9100.27
    Year3 2006 Year3Amt 9700.29
    Year4 2007 Year4Amt 10300.27
    Year5 2008 Year5Amt 10900.27
    Year6 2009 Year6Amt 9494.80

    I just don't know how I can ask database to look for the same years and at the same time plug in the years into the YearX fields while summing up the corresponding YearXAmts.

    There are 40 years total so 40 BegYear1-40 and 40 EndYear1-40 and of course corresponding amounts to each.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by tobabygu View Post
    I just don't know how I can ask database to look for the same years and at the same time plug in the years into the YearX fields while summing up the corresponding YearXAmts.
    The code I provided just does that. You can use it in the Form_Current event handler of the concerned form.
    Have a nice day!

  7. #7
    Join Date
    Nov 2011
    Posts
    103
    So it should be something like this but repeated?

    Private Sub Form_Open(Cancel As Integer)
    Dim i As Long

    For i = 1 To 40
    If Me.("BegYear1" & i).Value = Me.("EndYear1" & i).Value Then
    Me.("Year1" & i).Value = Me.("BegYear1" & i).Value
    Me.("Year1" & i & "Year1Amt").Value = Me.("BegAmt1" & i).Value + Me.("EndAmt1" & i).Value
    End If
    Next i
    End Sub

  8. #8
    Join Date
    Nov 2011
    Posts
    103
    Nevermind. I think I understand what you were trying to do here... But I am getting

    Compile Error:
    Expected: identifier or bracketed expression

    And would "Amt" in your calculation cover my format of YearXAmt?

    By the way, thanks for the help. Really appreciate it.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    On which line does the error occurs?
    Have a nice day!

  10. #10
    Join Date
    Nov 2011
    Posts
    103
    On all 3 starting from If. And if I ignore those error messages and just try to run my form I get

    Compile Error:
    Syntax error

    from the first line - If Me.("BegYear" & i).Value = Me.("EndYear" & i).Value Then

    Yellow highlight on Private Sub Form_Open(Cancel As Integer)

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Are there controls named "BegYear1", "BegAmt1", etc. actually on the form?
    Have a nice day!

  12. #12
    Join Date
    Nov 2011
    Posts
    103
    Yes. BegYear1, EndYear1, BegAmt1, EndAmt1, Year1, Year1Amt, & etc.

    Do I need to do Forms!CalendarYrAmt!BegYear1 instead?

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Normally not. If the code runs from the module associated with the form CalendarYrAmt, Me. should suffice.

    Try:
    Code:
    Me.Controls("BegYear" & i).Value = ...
    Have a nice day!

  14. #14
    Join Date
    Nov 2011
    Posts
    103
    Now I'm getting Method or data member not found with blue highlights on Controls during the last control line.

    Me.Controls("Year" & i & "Amt").Value = Me.Controls("BegAmt" & i).Value + Me.Controls("EndAmt" & i).Value

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Same question: Are there controls named "Year1Amt", "Year2Amt, etc., named "BegAmt1", "BegAmt2", etc., named "EndAmt1", "EndAmt2", etc.?

    And are these controls of type TextBox (some controls, such a Labels, don't have a Value property)?
    Have a nice day!

Posting Permissions

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