How to use loop through to create date ranges

[xzmilan]

Totally new to using loop through's but I have a feeling it's what I need.

I have two tables

tbl1 has a user ID and a change date, a date they updated their information

tbl2 has the user ID and an amount they asked to be reimbursed on a specifice date.

tbl1 change date is in one column. I want to get the sum of reiembursement from tbl2 but only between the correct date ranges from the column in tbl1.

It's somewhat easy to do in excel but i want this to be effecient and access should be able to compare row data in one column, i just don't know how.

tbl1 looks like this...

userID name Change Date Change Code Change Description
user1 5/27/2009 code1 changed name
user1 1/29/2010 code1 changed name
user2 3/30/2009 code1 changed name

etc..

in tbl2 there is

userID ChargeAmount Charge Date
user1 $100 5/31/2009
user1 $200 6/20/2009
user1 $2000 2/20/2010

So between 5/27/09 and 1/28/10, I need to know the charges, then from 1/29/10 up to the next change date the charges etc.... all for usr 1, then the same for user 2 etc.

So far i have a query that is giving me the attached...line s 9, 10, and 11 are the issue, and there can be more than 2 change dates, so this trend will just continue the charges for months 1/31/10, 02/28/10, and 3/31/10 should only show for the change date 1/21/10, and not anything before.

[ChipR]

We're going to need more information. From what you've described, tbl1 has one date, not a date range.

[ADezii]

1. If I understand you correctly, for user1 you would need to know the Total reimbursement from tbl2 for user1 between the Date Range 5/27/2009 to 1/28/2010. This Total reimbursement would be $300, namely $100 (5/31/2009) and $200 (6/20/2009) which fall within the Applicable Range. Is my thinking correct?

[xzmilan]

1. If I understand you correctly, for user1 you would need to know the Total reimbursement from tbl2 for user1 between the Date Range 5/27/2009 to 1/28/2010. This Total reimbursement would be $300, namely $100 (5/31/2009) and $200 (6/20/2009) which fall within the Applicable Range. Is my thinking correct?

Hi ADezii! Yes that is correct! The issue is the date changes come in the same column of the same table. Is there a way to do this?

[ADezii]

Hi ADezii! Yes that is correct! The issue is the date changes come in the same column of the same table. Is there a way to do this?

Let me put my thinking cap on and get back to you later.

[xzmilan]

Let me put my thinking cap on and get back to you later.

Thank you! Greatly appreciated.

[ADezii]

Thank you! Greatly appreciated.

Try this 'Experimental Logic' on your Data:

Dim MyDB As DAO.Database
Dim rst_1 As DAO.Recordset
Dim rst_2 As DAO.Recordset
Dim strCriteria As String

Set MyDB = CurrentDb
Set rst_1 = MyDB.OpenRecordset("SELECT * FROM tbl1;", dbOpenSnapshot)
Set rst_2 = rst_1.Clone

rst_2.Move 1

Debug.Print "User    Change Dt1  Change Dt2  Total Charge Amt."
Debug.Print "---------------------------------------------------------------"

With rst_1
  Do While Not rst_2.EOF
    If ![UserID] = rst_2![UserID] Then
      strCriteria = "[Charge Date] BETWEEN #" & ![Change Date] & "# AND #" & rst_2![Change Date] - 1 & _
                    "# AND [UserID] = '" & ![UserID] & "'"
      Debug.Print ![UserID] & " | " & ![Change Date] & " | " & rst_2![Change Date] - 1 & " | " & _
                   DSum("[ChargeAmount]", "tbl2", strCriteria)
    End If
      .MoveNext
      rst_2.MoveNext
  Loop
End With

rst_1.Close
rst_2.Close
Set rst_1 = Nothing
Set rst_2 = Nothing

[xzmilan]

Great, I am going to try this today and will let you know how it goes.

[xzmilan]

Great, I am going to try this today and will let you know how it goes.

Hi ADezii,

Thank you for the code..it is working so far.

This is what I got...

User Change Dt1 Change Dt2 Total Charge Amt.
---------------------------------------------------------------
5268340001 | 5/27/2009 | 1/20/2010 | 345706.16

Which is correct for the first date range. Now I need those charges by month. between the two date ranges.

And I should have charges for 1/21/2010 through 3/31/2010 which is my last date of charge data.