SQL string doesnt seem to be working

[HVACMAN24]

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
    [COLOR=red]Set rs = CurrentDb.OpenRecordset(SQLString)[/COLOR]
 
    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

 

[bob fitz]

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)=[B][COLOR=red]'" & LogNumberTemp & "'"[/COLOR][/B]
    [COLOR=black]Set rs = CurrentDb.OpenRecordset(SQLString)[/COLOR]
 
    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

The changed code is in Red

 

[HVACMAN24]

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

 

[bob fitz]

I'm glad we were able to help.