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.
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.
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.
RowId: AutoNumber, PrimaryKey
YearPart: Integer, Indexed (duplicates OK)
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.
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.
OK, so basically you need a VBA loop looking like:
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
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.
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