Results 1 to 7 of 7
  1. #1
    Join Date
    May 2013
    Posts
    7

    Unanswered: Loop through recordset

    I am in need of code that will loop through a recordset of loan numbers and populate the dates (from the second column) for each loan number into fields in a different table. They need to fill in the columns going across from earliest to most recent. There will be multiple loan numbers with up to 30 dates.

    Loan Dates
    123456789 08/20/13
    123456789 08/23/13
    123456789 08/17/13

    to this

    Loan Call Date 1 Call Date 2 Call Date 3
    123456798 08/17/13 08/20/13 08/23/13

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Attached is a sample database. Run the module and the input table data will be transposed as per your requirements.

    Note the output has only 10 columns for dates you will need to add columns for the maximum number of dates for a loan. Then adjust the code accordingly.
    Attached Files Attached Files

  3. #3
    Join Date
    May 2013
    Posts
    7

    Thumbs up

    Thanks Poppa Smurf.

    After a little tweaking with the numbers (Quantity) this worked great.
    I have 1 more issue with it, that I did not think about. If a Loan has more than 30 call dates, how would I go about only using the most recent 30 dates?

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    In your modified module do the following

    1. Add Dim date_counter As Integer this will be used to count the number of dates processed.

    2. In sqlcode statement "ORDER BY tbl_input.loan_number, tbl_input.loan_dates desc" add desc this will sort the records in loan number order ascending and and loan dates descending so that the latest date will appear first down to earliest.

    3. Add the lines in bold
    array_input(row_counter, col_counter) = .Fields(1)
    date_counter = 1
    ' move to the next record
    .MoveNext
    ' keep looping until the end of the file
    Do Until .EOF
    ' check if the loan number is the same as the previous record
    If .Fields(0) = temp_loan_number Then
    If date_counter < 30 Then
    ' store the loan date in the next column of the array
    col_counter = col_counter + 1
    array_input(row_counter, col_counter) = .Fields(1)
    date_counter = date_counter + 1
    End If
    Else
    ' store new loan number in variable
    temp_loan_number = .Fields(0)
    ' reset date_counter
    date_counter = 1

    The date counter will count each date stored in descending order until 30 records then no more records will be stored until a new loan number is processed then the date counter starts again from 1.

  5. #5
    Join Date
    May 2013
    Posts
    7

    Smile

    Thanks Poppa Smurf.

    Worked like a gem.

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Glad to assist.
    Note, I have only tested this code with 10 records for the same loan number. Have you tested more than 30 records for the same laon number and checked that the latest 30 dates are processed?

  7. #7
    Join Date
    May 2013
    Posts
    7
    Yes I have. Approx. 38k loans to be almost exact.

    I was using an old If DCount function that was taking upwards of 1 hour to run. This way it runs in a minute or so.

    I have plenty more issues to come.

    Again, Thanks

Posting Permissions

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