Data type mismatch in criteria expression (1 Viewer)

[shery1995]

Can someone check and advise why I'm getting this message.
Thankx in advance

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As Database
Dim LSQL As String
Dim Lrs As DAO.Recordset


Set db = CurrentDb()

'Retrieve last number assigned for Journal Number
LSQL = "Select max(MatterID) + 1 from tblClientMatter"
LSQL = LSQL & " where ClientID = '" & Me.ClientID & "'"

Set Lrs = db.OpenRecordset(LSQL)

'If no records were found, return an error
If Lrs.EOF = True Then
MatterID = 0
MsgBox "There was no entry found in the Matter table for This Client."

Else
'Determine new Journal Number
MatterID = Lrs("MatterID") + 1
Lrs.Close
Set Lrs = Nothing
Set db = Nothing
End If
End Sub

 

[JamesMcS]

Is clientID a number or text field? You've got it surrounded in ' which is making Access think it's a string.

 

[namliam]

Either or...

Either you are having this problem because you are not disambiguating
Dim db As DAO.Database
Dim Lrs As DAO.Recordset

OR...
LSQL = LSQL & " where ClientID = '" & Me.ClientID & "'"
You are searching for an ID which is usually a number, while here you ar esearching for a string/text...
LSQL = LSQL & " where ClientID = " & Me.ClientID & " "
Woudl be the way to fix it...

P.S. This has been sitting on my screen for a bit, might be I am providing answer to an answer