Recordset trouble...

[nicmar]

Hi,
I am trying to get this recordset to work. My SQL statement is correct, I checked it in the query builder grid and it worked. Is there something that I need to turn on or declare, that is not done, to get the recordset to work? Or is there something to turn on for the dbOpenSnapshot? I have beaten my head against the wall on this one!!

Here is my code:

Set gdbAll = CurrentDb
Dim strClass As String
Dim strTestNum As String

Dim rsStatistics As Recordset
Dim rsFailures As Recordset

strTestNum = [Forms]![TestScore]![cboTestName]
strClass = [Forms]![TestScore]![cboClass]

Debug.Print gstrSQL
  
gstrSQL = "SELECT [SDataQ].[Class], Count _
      ([SDataQ].strTestNum]) AS "
gstrSQL = gstrSQL & "NumberOfStudents, Avg _
      ([SDataQ].strTestNum]) AS txtAverage, "
gstrSQL = gstrSQL & "Min([SDataQ].[strTestNum]) AS _ 
      MinOfScore, "
gstrSQL = gstrSQL & "Max([SDataQ].[strTestNum]) AS _ 
      MaxOfScore "
gstrSQL = gstrSQL & "FROM [SDataQ] "
gstrSQL = gstrSQL & "WHERE SDataQ.Class= " & [Forms]! _
      [TestScore]![cboClass] & ";"
Set rsStatistics = gdbAll.OpenRecordset(gstrSQL, _ 
      dbOpenSnapshot)
  
If rsStatistics.RecordCount <> 0 Then
     txtMaxScore = rsStatistics!MaxOfScore
     txtMinScore = rsStatistics![MinOfScore]
     txtAverage = rsStatistics![AvgOfScore] 
End If

rsStatistics.Close

I appreciate any comments/help!

Thank You,
nicmar

 

[JamesLean]

What exectly is not working? Are you getting an error? If so, what is it and which line causes it? --James

 

[nicmar]

I am getting one of two error over and over. They are: Type Mismatch Or Syntax Error (missing operator) in 'SDataQ.Class=01CBE'. The Set rsStatistics = gdbAll.OpenRecordset(gstrSQL, dbOpenSnapshot) line is what I think is causing it. Like I said I tested my SQL and it is working, this is the line that is highlighted when I debug. Sorry for not including this info earlier.

Thanks,
nicmar

 

[JamesLean]

gstrSQL = "SELECT class, COUNT(strTestNum) AS NumberOfStudents, AVG(strTestNum) AS txtAverage, MIN(strTestNum) AS MinOfScore, MAX(strTestNum) AS MaxOfScore "

gstrSQL = gstrSQL & "FROM SDataQ WHERE class = '" & [Forms]![TestScore]![cboClass] & "' GROUP BY class;"

I've just tried to tidy that query up a bit. Only thing I really added was the single quotes around the value and the GROUP BY clause. Try this and see if it helps. --James

 

[nicmar]

Hey,
Thanks, I tried that and I get this error: Too few parameters.Expected 1. I have another question, maybe you can help me with, when I debug.print the Dim rsStatistics As Recordset tells me rsStatistics = nothing. As well as this line: Set rsStatistics = gdbAll.OpenRecordset(gstrSQL, bdOpenSnapshot) They both say the same thing when I debug.print, does that me there is a problem with them or are they supposed to be that way until they run through the SQL statement??

Thank You,
nicmar