Extract the value only for few columns dynamically from result set

ria.arora

Registered User.
Local time
Tomorrow, 00:55
Joined
Jan 25, 2012
Messages
109
Hi All,

I have table with following Columns:

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

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

Code:
    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 & ") &" '[COLOR=blue]--> Not Returning the value[/COLOR]
            ColumnStart = ColumnStart + 1
        Loop
        ColumnStart = ColumnStart + 1
        RowStart = RowStart + 1
        If rsTmp.EOF Then Exit For
        rsTmp.MoveNext
    Next count

Below line is not returing the value
objXLSheet.Cells(RowStart, ColumnStart) = " & rsTmp.Fields(" & i & ") &"

Please help me to correct this code...
 
1. Bad design. Tables should not have repeating columns like that. That data should be stored as rows and then you can pull the information and display it. But

2. Since you have it like that, the fix to your line of code is simply

objXLSheet.Cells(RowStart, ColumnStart).Value = rsTmp(i)
 
oh, and I would change from having i as an Integer to using something else as a LONG.
 
Thanks. I have designed in this way due to the requirment.

Thanks for the help.
 
Thanks. I have designed in this way due to the requirment.
Doing bad database design because someone who doesn't know database design principles tells you how to do it, is a bad idea and you, and them, will reap some pretty bad consequences I fear. It is gong to make it extremely hard to get the data back out in a meaningful way and so I think you are probably going to find yourself trying to come up with "band-aid" solutions a lot. (this comes from 15 years of Access experience, including doing what you are now doing.)

So good luck with it and I'm sure you'll probably be asking for help a lot when stuff isn't as easy as you thought it would be, simply because you went down the path of flawed database design.

(sorry to be so blunt, but it is what it is)
 
Last edited:
Yes are right Its not a good design but due to time limitations and requirements I have done in this way
 
Yes are right Its not a good design but due to time limitations and requirements I have done in this way

Funny you say that as that is funny. The time limitations you speak of remind me of that axiom, ” it always takes less time to do it right the first time than to go back and fix it” which is what you'll end up agreeing with down the line.
 

Users who are viewing this thread

Back
Top Bottom