Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

03Explorer

Technical User
Sep 13, 2005
304
US
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
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
 
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)
Code:
if RS.fields.count > yourIndex then

But I can shorten all of your code with that logic

Code:
            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
 
MajP

Are you saying

Code:
If RS.fields.count > yourIndex THEN

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

Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top