Extract the value only for few columns dynamically from result set

[riaarora]

Hi All,
I have table with following Columns:

Banker
Status
Week1
Week2
Week3
Week4
......
Week48
Week49
Week50
Budget
YearMonth

I need to extract the value only for few columns dynamically from result set. I have written following code for the same:

    i = 0
    strColumnsSQL = "Week" & CStr(gsReportingWeekNo)
    
    Do While i < 4
        i = i + 1
        strColumnsSQL = strColumnsSQL & ",Week" & CStr(gsReportingWeekNo) - i
    Loop
    strSelectSQL = "SELECT tbl_Consol_Weekly_NNA.[Banker], tbl_Consol_Weekly_NNA.Exclude, " & _
                    strColumnsSQL & _
                    " FROM tbl_Consol_Weekly_NNA " & _
                    " INNER JOIN tbl_Mapping ON tbl_Consol_Weekly_NNA.[Banker] = tbl_Mapping.[Revenue Producer]" & _
                    " WHERE [YearMonthWeek] = '" & txtReportingMonth & CStr(gsReportingWeekNo) & "'" & _
                    " AND " & strWhereCondition & "= '" & sTeamRegionName & "' " & _
                    " AND tbl_Mapping.[Exclude] = False " & _
                    " ORDER BY " & strWhereCondition & ", tbl_Consol_Weekly_NNA.[Private Banker]"
    Set rsTmp = gsPnPDatabase.OpenRecordset(strSelectSQL)
    
    i = 0
    ColumnStart = ColumnStart + 1
    Do While i < 4
        i = i + 1
        objXLSheet.Cells(RowStart, ColumnStart) = "Week " & CStr(gsReportingWeekNo) - i
        ColumnStart = ColumnStart + 1
    Loop
    rsTmp.MoveLast
    recount = rsTmp.RecordCount
        
    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp.Fields(0)
        ColumnStart = ColumnStart + 1
        i = 0
        Do While i < 4
            i = i + 1
            objXLSheet.Cells(RowStart, ColumnStart) = " & rsTmp.Fields(" & i & ") &" '--> Not Returning the value
            ColumnStart = ColumnStart + 1
        Loop
        ColumnStart = ColumnStart + 1
        RowStart = RowStart + 1
        If rsTmp.EOF Then Exit For
        rsTmp.MoveNext
    Next count

Below line is not returning the value

objXLSheet.Cells(RowStart, ColumnStart) = " & rsTmp.Fields(" & i & ") &"

Please help me to correct this code...

[June7]

Which 4 fields do you want to reference in the loop?

If you want the 4 week fields, they are in fields indexed as 2 through 5 because indexes begin with 0.

Try:

        For i = 2 to 5
            objXLSheet.Cells(RowStart, ColumnStart) = rsTmp.Fields(i)
            ColumnStart = ColumnStart + 1
        Next

The Exclude field in the query is not shown in the list of table fields.

The code that builds the week fields string looks like it will retrieve 5 fields, not 4. Also, the SQL will probably error if reporting week is <=4. If you modify to retrieve only 4 fields then will error on <=3. Consider:
If CStr(gsReportingWeekNo) - i > 0 Then strColumnsSQL = strColumnsSQL & ",Week" & CStr(gsReportingWeekNo) - i