Trouble with ADODB query

[galantis]

I am having trouble return the result when I code the SQL statement in VBA, but if I copy and paste the SQL straight to Query design it return the correct return.

Using ADODB, it gives me "S0" but Query Design gives me "01"

My table is
SiteCC
1921001
19210S0

and I want to return "01"

thankyou

Public Sub FindMaxSeqNum()
    Dim CurConn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim Curdb As Object
    Dim varInt As Variant
        
    Set Curdb = CurrentDb
    Set CurConn = New ADODB.Connection
    With CurConn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "data source=" & Curdb.Name
            .Open
    End With
    
    Set rst = New ADODB.Recordset
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.Open "SELECT Max(Right([SiteCC],2)) AS MaxNumber FROM [FireProofing] WHERE [SiteCC] Not Like " & "'" & "*" & "s" & "*" & "'", CurConn, , , adCmdText
    With rst
        If .BOF = True Then
            intMaxSeqNum = !MaxNumber
        End If
    End With
    
    rst.Close
    CurConn.Close
    
    Set rst = Nothing
    Set CurConn = Nothing
    Set Curdb = Nothing

    
End Sub

 

[krunalprajapati]

use MIN function insted of MAx in ur query as the last two charates are 01 and S0, which r considered as string not number, so for string 01 is MIN value.

hope this works

 

[RoyVidar]

Try using ADO wildchards, % in stead of * (and _ (underscore) in stead of ?, should single character wildchards be interesting)