Assigning value to a variable using SQL statement

[Phil Hood]

I am trying to assign a value to a variable (NoOfRides)
based on an SQL statement. When I run the procedure, I
get an error message saying "Run time error 13. Type
mismatch"

What am I doing wrong?


Dim dbsCurrent As Database
Dim rstPrepareAveragesData As Recordset
Dim strQuerySQL As String
Dim NoOfRides As Integer
Dim Points As Integer
Dim BonusPoints As Integer

Set dbsCurrent = CurrentDb

NoOfRides = "SELECT Count(Results.RideNo) AS
CountOfRideNo FROM Results HAVING ((([Results]![RiderID])
= " & _
Forms![Heat Edit]![Results Subform].Form![Combo18] & ")
AND (([Results]![ResultDescID])<>1 And ([Results]!
[ResultDescID])<>3));"

 

[Douglas J. Steele]

What are the data types of RiderID and ResultDescID? The code you have looks
correct if both are numeric.

On the other hand, you don't say where the error occurs, nor what version of
Access you're using.

Assuming it's Access 2000 or newer, you must have a reference set to both
DAO (or else the Dim dbsCurrent As Database would raise an error), but you
must also have the default ADO reference set.

When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO recordset,
you'll need to use Dim rstPrepareAveragesData As DAO.Recordset (to guarantee
an ADO recordset, you'd use Dim rstPrepareAveragesData As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[Ted]

before adding a reference to the 'Cycle_' variable, the code from which this
snippet was copied was working ok. at this point, it's getting an error 13
"type mismatch" error.

DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient And [Cycle_] = 0

any clues?

What are the data types of RiderID and ResultDescID? The code you have looks
correct if both are numeric.

On the other hand, you don't say where the error occurs, nor what version of
Access you're using.

Assuming it's Access 2000 or newer, you must have a reference set to both
DAO (or else the Dim dbsCurrent As Database would raise an error), but you
must also have the default ADO reference set.

When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO recordset,
you'll need to use Dim rstPrepareAveragesData As DAO.Recordset (to guarantee
an ADO recordset, you'd use Dim rstPrepareAveragesData As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to assign a value to a variable (NoOfRides)
based on an SQL statement. When I run the procedure, I
get an error message saying "Run time error 13. Type
mismatch"

What am I doing wrong?


Dim dbsCurrent As Database
Dim rstPrepareAveragesData As Recordset
Dim strQuerySQL As String
Dim NoOfRides As Integer
Dim Points As Integer
Dim BonusPoints As Integer

Set dbsCurrent = CurrentDb

NoOfRides = "SELECT Count(Results.RideNo) AS
CountOfRideNo FROM Results HAVING ((([Results]![RiderID])
= " & _
Forms![Heat Edit]![Results Subform].Form![Combo18] & ")
AND (([Results]![ResultDescID])<>1 And ([Results]!
[ResultDescID])<>3));"