Reading a Crosstab query column

[Steven Deetz]

I am using a crosstab query to total the amount of bills paid for the current year and the prior year. I need the totals for the current year (2009) and prior year (2008) to use in calculating outstanding balances. The problem is that when I call the crosstab as a recordset and try to access the 2009 field I get a type mismatch error with the below code.

I believe that my call to the crosstab query as a recordset is working because in debugging mode there is a string that is shown as "rs4!2009". This is almost what I want. It should be a statement of rs4!2009. Is there a way to drop the quotes?

I like the simplicity of the Crosstab query and the quick summarization by year. The recordset method appears to be a way to allow for column headings to change when a new year occurs. In other parts of Access you can state Column(1), etc. Can you do this with Recordsets? Thanks.

If rs4.EOF Then
curFSABills = 0
curFSABillPrior = 0
Else
Dim strColName As String
Dim strColNameB As String
Dim varColName As Variant
strColName = CStr("rs4!" & Year(Date))
strColNameB = CStr("rs4!" & (Year(Date) - 1))
rs4.MoveFirst
curFSABills = strColName
curFSABillPrior = Nz(strColNameB, 0)
End If

 

[HiTechCoach]

You might want to try this syntax:

strColName = CStr( rs4(Year(Date() ) ) )


This may also help:

Dynamic Report based on a Crosstab query