Error calling a function

  • Thread starter Thread starter Mario
  • Start date Start date
M

Mario

When I run the following query i keep getting an error
message "Data Type Mismatch in criteria expression".
Please help.

Follwoing is the query:
SELECT Get3ColData("Select Name, English, Math From
tblStudentMarks Where StudentID=1")

Following is the function:
Public Function Get3ColData(pstrSQL As String) As Variant
MsgBox (pstrSQL)
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
Dim strTempArray(2) As String
Dim iCounter As Integer
iCounter = 1

With rs
If Not rs.EOF Then
rs.MoveFirst
Do While Not .EOF
strTempArray(0) = rs(0)
strTempArray(1) = rs(1)
strTempArray(2) = rs(2)
rs.MoveNext
Counter = Counter + 1
Loop
End If
.Close
End With

Get3ColData = strTempArray
Set rs = Nothing
End Function


Following is the structure of table "tblStudentMarks" I
am using:

StudentID |Name |English |Math |Science
------------------------------------------------------
1|Mario | 56| 95| 98
2|Mary | 46| 85| 88

Please help., in how I can extract elements of array like
Get3ColData(1)., in the querry and display it in a column
of the query results
 
Well, you are attempting to return an array to your query. You really need to
build and return the string.

Also I don't think your query is complete. In Access I believe you are required
to have the FROM clause.
 
Back
Top