Date comparison 1

[RevenueSwede]

Hi!

I'm stuck. Short description of project. I have a table with filled with batallions (yep Army stuff) and their educational periods. Example:

Name StartDate EndDate
Bat1 1/1/2008 12/31/2008
Bat2 1/1/2007 6/30/2008
Bat3 7/1/2008 1/1/2009

Now I want to search between 1/1/2008 and 12/31/2008 and see per month how many Bat are active in education. I want to create a two dimensional array with 12 months, for each month I want the number of bat active and the total amount in my search, in this example it will always be 3. Are you with me?

The reason I want it as an 2 dimensional array is that I need to write the data to an Excelfile and then create a graph based upon that.

I have began with some code, rs is a recordset holding my search.

If Not rs.EOF Then

' --- Number of rows ---
vRowCount = rs.GetRows
rowCount = UBound(vRowCount)

' --- Number of months in period ---
monthCount = DateDiff("m", startDate, endDate)

' --- Loop through each month then loop through whole recordset to check if is active
For i = 1 To monthCount
' --- Check month
' --- ????

Do While Not rs.EOF

rs.MoveNext
Loop
Next
End If

 

[RevenueSwede]

If you have any other solution to this please let me know! Is it possible to use cross tab queries in any way?

 

[lespaul]

what do you want your final results to look like?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[RevenueSwede]

Let's say my resultset of my search is 6 rows and search is from 1/1/2008 to 12/31/2008 the result I want would look like this:

1 2 2 3 3 3 0 0 6 6 4 4
6 6 6 6 6 6 6 6 6 6 6 6

The period is 12 months, for january only 1 Bat is active in education, february 2 Bats are active and so on. Six is the total number of possible Bats.

The user's will be able to set start date and end date to what they want, for example 1/1/2008 to 6/30/2009.

 

[lespaul]

you mean:[tt]
Jan Feb Mar Apr May June July Aug Sept.....
# of Bats 1 2 2 3 3 3 0 6 6 ...

[/tt]



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[RevenueSwede]

Exactly. But I solved it Now I just have to put it in an array. Presentation is due tomorrow, now I have the Excel part left.

If Not rs.EOF Then

' --- Number of total rows
vRowCount = rs.GetRows
rowCount = UBound(vRowCount)

' --- Number of months
monthCount = DateDiff("m", startDate, endDate) + 1
' --- Number of bats for current month
ioCount = 0
currentDate = startDate
For i = 1 To monthCount
rs.MoveFirst
Do While Not rs.EOF
If ((rs!StartDate <= currentDate) And (rs!EndDate >= currentDate)) Then
ioCount = ioCount + 1
End If
rs.MoveNext
Loop
' --- Reset month counter
ioCount = 0
currentDate = DateAdd("m", 1, currentDate)
Next

 

[lespaul]

well, good for you....good luck on the next step!

Leslie

 

[fergler]

I'm trying to do something very similar to this. I need to generate a report based upon committed effort. For example, an employee has agreed to commit 50% of his time to a project with an effective start date of 1/1/07 and an effective end date of 12/31/10. I would like the report to show a month by month brakedown of this:

1/1/07 2/1/07 3/1/07 4/1/07 (etc.)
50% 50% 50% 50%

The person running the report would enter a start date and an end date for the report, so they could view as many or few months as needed. My knowledge of VBA code is minimal and very self taught, so I'm not too sure how to go about this. Would a module be created for the report on load? Would the report need to be a dynamic report? Any help I could get get, particularly with the code part, would be greatly appreciated.