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.
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?
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
' 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.
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?