Type mismatch error

[BenjiS]

Hi! My first post in this excellent forum is a question:

I am running a report in Access97, it comes from a query called "Select Date Range". I have this code so far in the Detail_Print sub.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim StrTSInvNum As String
Dim StrUSFInvNum As String
Dim StrTA002 As String
Dim rs1 As Database
Dim db As Database

Set rs1 = db.OpenRecordset("Select Date Range", dbOpenTable)

Text44.Form.Visible = True
Label43.Form.Visible = True
        
    If rs1!ChainCode = "TA002" Then
        StrTSInvNum = rs1!TruckStopInvoiceNum
        Text44.Form.Caption = Left(StrTSInvNum, 2)
    Else
        Text44.Form.Visible = False
        Label43.Form.Visible = False
    End If

    If rs1!ChainCode = "US004" Then
        StrUSFInvNum = rs1!TruckStopInvoiceNum
        Text44.Form.Caption = StrUSFInvNum
    Else
        Text44.Form.Visible = False
        Label43.Form.Visible = False
    End If

End Sub

My problem is that it gets to this line:

If rs1!ChainCode = "TA002" Then

and tells me "Type Mismatch" with

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

highlighted and the "=" selected. The ChainCode field is from the query "Select Date Range" which queries a table where ChainCode comes from, and it is set as a Text data type.

I have never had this problem before with comparing data to another. I have a feeling the ChainCode data type and the TA002 I'm comparing it to aren't the same. I would like to avoid changing the data type in the main table it originates from.

Any ideas would be welcome.

BenjiS

 

[RickSpr]

The statement:

    Dim rs1 As Database

should be:

    Dim rs1 As Recordset

Actually, it's surprising you didn't get the error on the "Set rs1 = db.OpenRecordset" line. OpenRecordset returns a recordset object, which can't be used to set a database object. I would have expected that to generate a Type Mismatch error.

You'll be getting an error on the Set line later anyway, because you haven't set the db variable to anything first. You need to put this before the existing Set statement:

    Set db = CurrentDb

BTW, you're mistaken that it "gets to this line" (meaning that the code has run to that line). When an error causes the procedure heading to be highlighted in yellow, it indicates a compile-time error. If you haven't compiled the module yourself (menu Debug>Compile), Access compiles it the first time you try to execute something in it. It's compiled all at once, not line by line. None of the code runs until the whole module is compiled.

It's a good practice to always hit Debug>Compile after changing a module, both because it avoids confusion about whether it's a compile-time or run-time error, and because resetting execution (hitting the Stop button) can leave stuff not cleaned up very well. That sometimes causes later errors that seem somewhat random.

 

[Telsa]

Why rs1!ChainCode = "TA002" instead of rs1.Chaincode...???

 

[RickSpr]

In fact, both will usually work, at least when referring to controls on forms. There's actually a difference, but it doesn't matter in this case. For an explanation, see the Access Help topic "Use the ! and . (dot) operators in expressions".

 

[BenjiS]

Thanks all. I think what I need is how to define the query that the report is using, where I can compare variables to it.