Run time error on SQL

[aziz rasul]

I have the following snippet of code: -

    strSQL = "SELECT " & strField & " from " & strTable & " WHERE " & strField & ">0 Order by " & strField & ";"
    Set rst = CurrentDb.OpenRecordset(strSQL)

On the last line I get error 13 i.e. 'Type mismatch'. Any ideas why this should be?

Even if I write

strSQL = "SELECT AdmissionLOS from StrokeLOSDATA;"

 

[namliam]

The fieldname that is stored in strField is probably a text field, not a number...

 

[aziz rasul]

strField is a Number data type i.e. Double.

Sorry to hear about your sister-in-law.

 

[aziz rasul]

Here's the heading of the function

Function Median(strTable As String, strField As String) As Single

 

[chergh]

strField is a string, or at least you are passing it to the function as a string so if in the field is a double datatype then you need to use the following:

    strSQL = "SELECT " & strField & " from " & strTable & " WHERE " & cdbl(strField) & ">0 Order by " & strField & ";"
    Set rst = CurrentDb.OpenRecordset(strSQL)

 

[aziz rasul]

I got the same error message but on the strSQL line rather than the Set rst line.

 

[ecawilkinson]

Just a thought. Is Rst an DAO or ADODB.recordset?
try this:

Dim rst as DAO.Recordset

If you have references to ADO and DAO, it is possible to select the wrong Recordset object in the DIM statement.

HTH,
Chris

 

[namliam]

Then either the DAO thing that Wilkinson said (Hey of the razors?? LOL How many times do you get that question?? )

Or we need more info on the complete coding around this problem.

 

[aziz rasul]

This was worrying me for a while. You guys were right. When I typed dao, it didn't automatically change it to DAO. I looked at Tools|References and the DAO library was not selected. On my smaller version where I initially tested it, it did have the DAO library.

Thanks a bunch guys.