pull numbers from string and get max value

[ths]

Hi,
I have a feeling I'm missing something very basic here... grateful for a steer.

I have a series of IDs in an 'articles' table stored as text, e.g.
hb-123456789-e-068
hb-123456789-e-0069
hb-123456789-e-70
hb-123456789-e-00027

and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.

The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.

Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:

 selectedERef = Val(Right(rs![masterArticleID], Len(rs![masterArticleID]) - InStrRev(rs![masterArticleID], " - ")))

Public Function MaxArticleERef(hbID As Long) As Variant
On Error GoTo err_handler
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
 
Dim maxERef As Variant
Dim selectedERef As Variant
 
maxERef = Null
selectedERef = Null
 
Set db = CurrentDb
 
strSql = "SELECT [masterArticleID], [handbookID] " & _
            "FROM [tblArticles] " & _
            "WHERE [handbookID] = " & hbID & "AND [masterArticleID] Is Not Null;"
               
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)


If Not rs.EOF Then
    rs.MoveFirst
    Do While Not rs.EOF
       selectedERef = Val(Right(rs![masterArticleID], Len(rs![masterArticleID]) - InStrRev(rs![masterArticleID], " - ")))
             
       If maxERef > selectedERef Then

       Else
             maxERef = selectedERef
       End If
       rs.MoveNext
    Loop
End If

rs.Close
    
MaxArticleERef = maxERef
    
Exit_Handler:
    Set rs = Nothing
    Set db = Nothing
    Exit Function

err_handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "MaxArticleERef()"
    Resume Exit_Handler
End Function

 

[pr2-eugin]

ths, that's because you are looking for the wrong String.. You are looking for a hyphen that has two spaces around it, based on the data you do not have that.. Try..

Val(Right(rs![masterArticleID], Len(rs![masterArticleID]) - InStrRev(rs![masterArticleID], [COLOR=Red][B]"-"[/B][/COLOR])))

 

[ths]

Thanks, Paul. I knew I'd been staring at this too long. That was it, of course. I grabbed what I had tested in the query, and I think when I pasted it might have inserted those spaces? who knows... in any case, huge thanks for spotting that.

Tim

 

[pr2-eugin]

No problem ! Good Luck !