Variable not defined

[gblack]

In the on clkick event I have code that is not running.

... Dim strCurrentYear As String
Dim intCurrentYear As Integer
Dim dtLastYear As Date
Dim intLastYear As Integer
Dim strLastYear As String
Dim strSubCycle As String
'Add dtThisYear to account for the changing year
Dim dtThisYear As Date
Dim db As Database
Dim rst As Recordset


'Get the Cycle Number and the Subcycle
strCycleNum = Forms!frmreports.[lstNetChk].Value
strSubCycle = Forms!frmreports.[NetChkSubCycle].Value
strSubCycle = "'" & strSubCycle & "'"


Set db = CurrentDb
'Get the year
strSQL = "SELECT Max([OCSE Cycle Raw Data].YEAR) as MaxOfYear " & _
"FROM [OCSE Cycle Raw Data] " & _
"WHERE (([OCSE Cycle Raw Data].[OCSE CYCLE])=" & strCycleNum & ") " & _
"AND (([OCSE Cycle Raw Data].SUBCYCLE)=" & strSubCycle & ");"

Set rst = db.OpenRecordset(strSQL)

intCurrentYear = rst(MaxOfYear) ....

Access for some reason doesn't understand MaxOfYear. Although when I run the query by hand it comes out to 2005. What am I doing wrong here?

 

[NJudson]

In the on clkick event I have code that is not running.

... Dim strCurrentYear As String
Dim intCurrentYear As Integer
Dim dtLastYear As Date
Dim intLastYear As Integer
Dim strLastYear As String
Dim strSubCycle As String
'Add dtThisYear to account for the changing year
Dim dtThisYear As Date
Dim db As Database
Dim rst As Recordset


'Get the Cycle Number and the Subcycle
strCycleNum = Forms!frmreports.[lstNetChk].Value
strSubCycle = Forms!frmreports.[NetChkSubCycle].Value
strSubCycle = "'" & strSubCycle & "'"


Set db = CurrentDb
'Get the year
strSQL = "SELECT Max([OCSE Cycle Raw Data].YEAR) as MaxOfYear " & _
"FROM [OCSE Cycle Raw Data] " & _
"WHERE (([OCSE Cycle Raw Data].[OCSE CYCLE])=" & strCycleNum & ") " & _
"AND (([OCSE Cycle Raw Data].SUBCYCLE)=" & strSubCycle & ");"

Set rst = db.OpenRecordset(strSQL)

intCurrentYear = rst(MaxOfYear) ....

Access for some reason doesn't understand MaxOfYear. Although when I run the query by hand it comes out to 2005. What am I doing wrong here?

Try re-writing the WHERE clause in the query to say:

"WHERE (([OCSE Cycle Raw Data].[OCSE CYCLE])='" & strCycleNum & "') " & _
"AND (([OCSE Cycle Raw Data].SUBCYCLE)='" & strSubCycle & "');"


When you use a variable in a criteria you need to have the variable surrounded by single quote, double quote and ampersand like this '" & variable & "'

 

[gblack]

Happy

I just used a ! and it seems to work fine now.

rst!MaxYear

Now Access is happy and when Access is happy I'm happy.