Another dept at work wanted me to look at a problem they are having in there database. I've fixed a couple of the problems but now I'm down to this problem regarding a SQl string, and recordset and could use some help if you can.

I'm getting the error "Run-Time error 3464, Data type mismatch in criteria expression." and when I hit debug it goes to the set rs line of code. I'm almost positive its something in the SQLString variable but I'm not sure what. Everything looks ok to me. Here is the whole sub that the problem is in.


Sub LogNumberGenerate()
Dim rs As DAO.Recordset
Dim LogNumberTemp As String, Temp As String
Dim SQLString As String, BoFile As Boolean, EoFile As Boolean

LogNumberTemp = Right(Me.UnitTypeL.Value, 2) & Left(Me.TonsN, 1) & Me.CellsN

SQLString = "SELECT left(LogNum,4) as Expr1, Max(Right(LogNum,Len(LogNum)-4)) as Expr2 FROM FurnaceLog_tb GROUP BY Left(LogNum,4) HAVING left(LogNum,4)=" & LogNumberTemp
Set rs = CurrentDb.OpenRecordset(SQLString)

BoFile = rs.BOF
EoFile = rs.EOF

If BoFile And EoFile Then
Temp = "00"
Else
If rs("Expr2").Value < 9 Then
Temp = "0" & rs("Expr2").Value + 1
Else
Temp = rs("Expr2").Value + 1
End If
End If

Me.LogNumberN = LogNumberTemp & Temp
rs.Close

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

LoadFurnaceHistory

Dim stDocName As String
stDocName = "LogLabeLReport"
DoCmd.OpenReport stDocName, acNormal
End Sub

Hi

Your varriable "LogNumberTemp" is declared as a string, so mayby this would work:Sub LogNumberGenerate()
Dim rs As DAO.Recordset
Dim LogNumberTemp As String, Temp As String
Dim SQLString As String, BoFile As Boolean, EoFile As Boolean

LogNumberTemp = Right(Me.UnitTypeL.Value, 2) & Left(Me.TonsN, 1) & Me.CellsN

SQLString = "SELECT left(LogNum,4) as Expr1, Max(Right(LogNum,Len(LogNum)-4)) as Expr2 FROM FurnaceLog_tb GROUP BY Left(LogNum,4) HAVING left(LogNum,4)='" & LogNumberTemp & "'"
Set rs = CurrentDb.OpenRecordset(SQLString)

BoFile = rs.BOF
EoFile = rs.EOF

If BoFile And EoFile Then
Temp = "00"
Else
If rs("Expr2").Value < 9 Then
Temp = "0" & rs("Expr2").Value + 1
Else
Temp = rs("Expr2").Value + 1
End If
End If

Me.LogNumberN = LogNumberTemp & Temp
rs.Close

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

LoadFurnaceHistory

Dim stDocName As String
stDocName = "LogLabeLReport"
DoCmd.OpenReport stDocName, acNormal
End SubThe changed code is in Red

I added your code and its working like it should. Thanks a lot.

I'm glad we were able to help.