VBA Recordset SQL returns up to 4 columns (how to identify if column 4 exists)

[03Explorer]

I have a query that is a pivot table output. The column names are populated out of values found in source table

[Code tblAssociatePerformance]
aID AssociateID Year Rating
--- ----------- ---- ------
1 as34afeet 2017 Exceeds7
2 dafsjkl34 2017 Meets7
3 as34afeet 2016 Needs Improvement6
4 as34afeet 2015 Meets5
5 dafsjkl34 2016 Meets6
6 dafsjkl34 2015 Meets5
7 Advgef4d3 2017 Meets7
8 Advgef4d3 2016 Meets6
[/Code]

Here is my VB code

        Dim db As Database
        Dim rs As DAO.Recordset
        Dim SQL As String

        SQL = "TRANSFORM First(tblAssociatePerformance.Rating) AS FirstOfRating " & vbCrLf & _
        "SELECT tblAssociatePerformance.AssociateID " & vbCrLf & _
        "FROM tblAssociatePerformance " & vbCrLf & _
        "WHERE [Year] >= Year(Date())-3 AND tblAssociatePerformance.AssociateID = " & Chr(39) & [Forms]![frmAssociateLandingPage]![txtHoldAssociateID] & Chr(39) & " " & vbCrLf & _
        "GROUP BY tblAssociatePerformance.AssociateID " & vbCrLf & _
        "Order by tblAssociatePerformance.Year Desc " & vbCrLf & _
        "PIVOT tblAssociatePerformance.Year;"
            Debug.Print SQL
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset(SQL)
        'Debug.Print "Record Count is: " & rs.RecordCount
        
        If Not (rs.EOF And rs.BOF) Then
            rs.MoveFirst 'Unnecessary in this case, but still a good habit
            Do Until rs.EOF = True
                                
                If Not rs.NoMatch Then
                    Me!txtYear1 = rs(1).Name
                    Me!txtValue1 = rs(1).value
                End If
                
                If Not rs.NoMatch Then
                    Me!txtYear2 = rs(2).Name
                    Me!txtValue2 = rs(2).value
                End If
                
                If Not rs.NoMatch Then
                    Me!txtYear3 = rs(3).Name
                    Me!txtValue3 = rs(3).value
                End If
                    '-THIS CODE WON'T NEED TO LOOP BECAUSE IT'S RETURNING ONE ROW
                rs.MoveNext
            Loop
            Else
            '    MsgBox "There are no records in the recordset." & vbCr & _
             '           "No Records to show"
        End If

Note that AssoiateID = 'Advgef4d3' does not have all three years? This code needs to do something with the 3rd year value... the code only returns this for the one associate

For Associate = 'Advgef4d3'

AssociateID     2017        2016
-----------     ----        ----
Advgef4d3       Meets7      Meets6
{nothing returned for 4th column}

For Associate = 'as34afeet'

AssociateID     2017        2016                   2015
-----------     ----        ----                   ----
as34afeet       Exceeds7    Needs Improvement6     Meets5

How do I trap for the missing column (as seen for example 'Advgefd3')

Note in my code I've been testing [not isnull(rs(3)) Then] but that along with rs(3).EOF True are failing...
How can I test if rs(3) or rs(2) or even rs(1) exists?

Thanks

 

[MajP]

you just need to check the count of the fields.
if you are looking for RS(1) you need more than one field (2 fields)

if RS.fields.count > yourIndex then

But I can shorten all of your code with that logic

            Do Until rs.EOF = True
                for I = 1 to rs.fields.count - 1
                   me.controls("txtYear" & I) = rs.fields(i).name
                   me.controls("txtvalue" & I) = rs.fields(i).value
                next i 
                rs.MoveNext
            Loop

 

[03Explorer]

MajP

Are you saying

If RS.fields.count > yourIndex THEN

if I am returning 4 columns, would my code look like this?

If RS.fields.count > 3 THEN {blah blah} End If

I use three because count begins at 0 (ZERO) so 3 would be FOUR columns?

*trying to understand the language of yourIndex

 

[MajP]

No. Imagine it returns 2 fields. AssociateID and 2017. The count is 2 the index you care about is 1.
So
If rs.fields.count > 1 then
Me!txtYear1 = rs(1).Name
Me!txtValue1 = rs(1).value
then on your next when you need to check if
if rs.fields.count > 2
Me!txtYear2 = rs(2).Name
Me!txtValue2 = rs(2).value