Hello folks - I am in need of some assitance in trying to get a recordset to open in my program. Let me give you the details. I am building an application in VB6 that feeds from data in an A2K DB. I am trying to generate a recordset based on a specific field (CaseNumber (who's data type is AutoNumber)). The CaseNumber is data passed from another form. Here is the code that I am using:

Dim cxn As ADODB.Connection
     Dim rst As ADODB.Recordset
     Dim strSQL As String
    
     Set cxn = New ADODB.Connection
     Set rst = New ADODB.Recordset

     With cxn
          .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
               "Cocuments and SettingsAll UsersDocumentsPNR DoctorPNR Doctor.mdb"
          .Open
     End With

     strSQL = _
          "SELECT ErrorData.CaseNumber " & _
          "FROM ErrorData " & _
          "WHERE ((ErrorData.CaseNumber) =  '" & frmAgentSearch.intCaseNumber & "');"

     rst.Open strSQL, cxn, adOpenStatic, adLockReadOnly, adCmdText

When I run this I get the following message:

Run Time Error '-2147217913 (80040e07)':
Data type mismatch in criteria expression.


I don't know what this means, what is causing it, or how to fix it. If anybody can provide me with any ideas of a solution I would really appreciate it...!

You're putting ' around the number, making it a string, just remove the '

____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)

Thank you JLRodgers - you saved the day..!!! I thought I had already tried that but obviously not.

Since this is the second question I've seen raised regarding this error (in the past week) I thought I would post an explanation for some folks. There are times when this error can appear for strange reasons, but for the most part you get this error when you have assigned a variable a value that doesn't match the type you have defined for that variable...eg.,

'Simple Type Mismatch Error Dim x As Integer 'Declare the Variable, and Define it's Type as an INTEGER x = "String" 'Attempt to Assign the Variable a STRING Value

However...this is acceptable...

'Type match Dim x As Integer x = 1 'This is OK x=1.1111111111 'This is ALSO OK, though x will still only equal 1 (an integer)

As I mentioned, this error can arise from a number of diff possibilities, but this is most common, and indeed, its also a variation of what Kenny ran into. And so the name of the error is actually quite descriptive of the error itself...you could say they "match"...(tee-hee)


FYI:

quote:

---

From VB Documentation:
Type mismatch

-The variable or property isn't of the correct type. For example, a variable that requires an integer value can't accept a string value unless the whole string can be recognized as an integer.
Try to make assignments only between compatibledata types. For example, an Integer can always be assigned to a Long, a Single can always be assigned to a Double, and any type (except auser-defined type) can be assigned to a Variant.

-An object was passed to aprocedure that is expecting a single property or value.
Pass the appropriate single property or call a method appropriate to the object.

-A module or project name was used where an expression was expected, for example:
Debug.Print MyModule
Specify an expression that can be displayed.

-You attempted to mix traditional Basic error handling with Variant values having the Error subtype (10, vbError), for example:
Error CVErr(n)

To regenerate an error, you must map it to an intrinsic Visual Basic or a user-defined error, and then generate that error.

A CVErr value can't be converted to Date. For example:
MyVar = CDate(CVErr(9))

Use a Select Case statement or some similar construct to map the return of CVErr to such a value.

-At run time, this error typically indicates that a Variant used in an expression has an incorrect subtype, or a Variant containing anarray appears in a Print # statement.
To print arrays, create a loop that displays each element individually.

---

Thanx Bean. I think I've got it now.