SQL syntax error 1

[nicmar]

Hey,
I am trying to do an openrecordset, I am pretty sure that my SQL statement is correct. The problem I am having is with the Set rsStatistics line. I get one of two errors:
Syntax error (missing operator) in query expression 'SDataQ.Class=01CBE'. OR Type Mismatch. No matter what I do it does not like the Set rs... line. Any suggestions??

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

Thank You in advance! I greatly appreciate it.
nicmar

 

[KenReay]

Hi

put a breakpoint on Set rsStatistics , when code stops, print gstrSQL, in debug window, copy the SQL and paste it into the SQL view of the query builder grid, then swith to design view, Access will highlight the error for you, let the machine do the work! Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[nicmar]

Hi,
I let the machine do the work! My SQL statement is correct. The problem I am having is with the Recordset line. Is there something that has to be turned on or declared somewhere to run a recordset, or dbOpenSnapshot? Does it matter if the database is ADO or DAO?

Thank you,
nicmar

 

[DonQuichote]

My guess is that you have forgotten the quotes in SQL in the form name (the WHERE clause). You can single quotes in SQL, which makes nicer source code in VB(A). The database itself is not ADO or DAO. both ADO and DAO are libraries that can be used to connect to databases.

Best regards

 

[nicmar]

Hey,
Thanks, I did forget the single quotes, but I still 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??

Also, thanks for the DAO and ADO info.

Thank You,
nicmar