03Explorer
Technical User
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
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
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
[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
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
Code:
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