VBA SQL SELECT with variable field name

[RichTea88]

I'm having a problem trying to create a sql statement to select a recordset/value from a table where I'm passing a variable in as a field name. When going through the code line by line when it gets to the bold section rst = nothing. So I'm assuming there's something wrong with the sql but I'm not getting any errors so it's hard to tell. any help would be greatly appreciated.


Here's basically what I'm using


Sub Calculate()
Dim DB As Database
Dim rst As DAO.Recordset
Dim sqlstr As String
Dim Year As Integer
Dim Round As String
Dim Con As String

Round = Forms!CurrentLicencesForm!Round
Year = Forms!CurrentLicencesForm!Year1

sqlstr = "SELECT LicenceRoundsSummary.[Round " & Round & "] FROM LicenceRoundsSummary WHERE (LicenceRoundsSummary.Year = " & Year & ");"

Set rst = CurrentDb.OpenRecordset(sqlstr)

Con = "[Round " & Round & "]"


If IsNull(rst.Fields(Con)) = True Then
Val = 0
Else
Val = rst.Fields(Con)
End If


Forms!CurrentLicencesForm!Licence_Rental_1 = Val




Cheers,

R

[RichTea88]

Forget it, Just figured out that the person who set up the table used text for a field that should contain numbers. Problem solved