You guys think you are sooooo smart. It's perfect. Thank you.
Why did I think that this had to be so difficult.
--
Linda
"vanderghast" wrote:
> Looping is so... 2008.
>
> Am I wrong if your goal is to get, from each record in BudgetAllocation, to
> get its BeginDate and EndDate, and to generate one record for each date
> between these two calendar dates?
>
>
> If so, easily done in SQL, no loop, but with the help of another table,
> Iotas, one field, Iota, its primary key, with values from 0 to 999 (easy to
> fill it from Excel and import those values in the Iota table of Access).
>
>
> In a new query, bring BudgetAllocation and Iotas.
> In the grid, bring the information fields from BudgetAllocation.
>
>
> Add a computed column:
>
>
> CurrentDate: [BudgetAllocation].[StartDate] + [Iotas].[iota]
>
> and add the criteria: <= [BudgetAllocation].[EndDate]
>
>
> and... that's all folk!
>
> You don't belive me? Try it! And you will see how Looping is so ... the old
> way... No loop, no recordset, almost no code (SQL is code, but you did it
> ... graphically! ) Why not ?
>
>
> Sure, maybe that was not what you wanted to do, in the first place... :-)
>
>
>
>
> Vanderghast, Access MVP
>
>
>
>
>
> "lgray" <(E-Mail Removed)> wrote in message
> news:E05B8513-7688-457F-AD11-(E-Mail Removed)...
> >I preface this with...Yes I am quite a novice in VBA......
> > In a table, I have two different criteria that I need to loop through to
> > build a dataset. The fields that I need to Loop through are Project_ID
> > AllocationDesc (P1, P2....P9). Once I've reached the AllocationDesc Loop
> > I
> > will then be running code that Increments Beginning Dates for a dataset.
> > I
> > have this part of the Code working. But I am having difficulty getting a
> > loop through the Project_ID's then another loop through the sub grouping
> > of
> > AllocationDesc in order to run my current code.
> >
> > Below is a sample of dataset:
> > Project_ID AllocationDesc BeginDate
> > 1 P1 8/25/2011
> > 1 P2 11/1/2011
> > 1 P3 1/7/2012
> > 1 P4 3/15/2012
> > 1 P5 5/21/2012
> > 1 P6 7/28/2012
> > 1 P7 10/3/2012
> > 1 P8 12/10/2012
> > 1 P9 2/15/2013
> > 2 P1 3/14/2012
> > 2 P2 4/22/2012
> > 2 P3 5/31/2012
> > 2 P4 7/9/2012
> > 2 P5 8/17/2012
> > 2 P6 9/25/2012
> > 2 P7 11/3/2012
> > 2 P8 12/12/2012
> > 2 P9 1/20/2013
> > 3 P1 5/19/2012
> > 3 P2 6/17/2012
> > 3 P3 7/16/2012
> >
> >
> > And below is Code that I will be using once I get into the AllocationDesc
> > Level.
> >
> > Private Sub BudgetAllocation()
> >
> > Dim rs As DAO.Recordset
> > Dim dStartDate As Date
> > Dim iEstDays As Integer
> >
> > iEstDays = 1
> >
> > Set rs = CurrentDb.OpenRecordset(tbl_BudgetAllocation)
> >
> > If rs.RecordCount <> 0 Then
> > rs.MoveFirst
> > 'Initilize the first StartDate (because there's no previous record)
> > dStartDate = rs.Fields("BeginDate")
> > dEndDate = rs.Fields("EndDate")
> > dProject_ID = rs.Fields("Project_ID")
> > dConstruction_Start_Date = rs.Fields("Construction_Start_Date")
> > dProjected_InService_Date = rs.Fields("Projected_InService_Date")
> > dDuration = rs.Fields("Duration")
> > dDaysPerPeriod = rs.Fields("DaysPerPeriod")
> > dAllocationDesc = rs.Fields("AllocationDesc")
> > dAllocationDate = rs.Fields("AllocationDate")
> > dAllocationAmt = rs.Fields("AllocationAmt")
> > dAllocationAmtPerDay = rs.Fields("AllocationAmtPerDay")
> >
> > 'Loop the rs
> > While dStartDate <= dEndDate
> > 'Update each record
> > With rs
> > .AddNew
> > rs.Fields("Project_ID") = dProject_ID
> > rs.Fields("BeginDate") = dStartDate
> > rs.Fields("EndDate") = dEndDate
> > rs.Fields("Construction_Start_Date") = dConstruction_Start_Date
> > rs.Fields("Projected_InService_Date") = dProjected_InService_Date
> > rs.Fields("Duration") = dDuration
> > rs.Fields("DaysPerPeriod") = dDaysPerPeriod
> > rs.Fields("AllocationDesc") = dAllocationDesc
> > rs.Fields("AllocationDate") = dAllocationDate
> > rs.Fields("AllocationAmt") = dAllocationAmt
> > rs.Fields("AllocationAmtPerDay") = dAllocationAmtPerDay
> > .Update
> > End With
> > 'Reset dStartDate for the next record
> > dStartDate = DateAdd("d", iEstDaysm, dStartDate + 1)
> >
> > Wend
> > rs.MoveNext
> > End If
> >
> > rs.Close
> > Set rs = Nothing
> >
> > End Sub
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> > Linda
>
|
Similar Threads
