Finding the max value of a mixed string in Access 2010

[klaidlaw]

I am trying to update a database for non-profit organization. Their membership numbers are a combination of the first letter of the surname plus an incremented number (i.e. L345). Currently, the users figure out the new number manually. I need help automating the new number on a new member form.

I have never done VBA coding, and this has become a nightmare of me:banghead:.
I thought I could find the first letter of the surname in the form on a before change by doing a Left([LastName], 1) on the form and then doing the same thing on the MemID field in tblMembers. What I can't seem to figure out is how to then combine the first letter result with the rest of the string and find the max number in the existing MemID with that particular letter to increment it for the new ID. Do I use a DMax statement? I tried setting up a Do Until Loop, but I got hopelessly lost. I don't think I'm stupid, but I am beginning to have my doubts.

If anybody can point me in the right direction I would really appreciate it. I don't necessarily want somebody to write the code, just point me in the right direction so I can try something else. My head is getting sore. Nothing like starting VBA with a complex question!

 

[Beetle]

Just to be clear, the numeric portion of the value doesn't just continuously increment, it increments separately for each letter? So you have something like;

A100
...
...
A199

B100
...
...
B268

etc.

 

[klaidlaw]

Just to be clear, the numeric portion of the value doesn't just continuously increment, it increments separately for each letter? So you have something like;

A100
...
...
A199

B100
...
...
B268

etc.

That is correct. Each letter/number combination increments separately.
What I had tried was:

Private Sub LastName_BeforeUpdate()
Dim strLeftLetter As String
strLeftLetter = Left([LastName], 1)
Dim intbiggestsofar As Integer
intbiggestsofar = 0

Dim db As DAO.Database
Dim rstMembers As DAO.Recordset
Set db = CurrentDb
Set rstMembers = db.OpenRecordset("Members")
Do While Not rstMembers.EOF
If Left([MemID], 1) = strLeftLetter Then 'check to see if this record starts with the right first letter
Dim intNumber As Integer
intNumber = (Val(Right([MemID], 1, 99))) 'This isn't correct. A friend who programs Java had suggested it
If intNumber > intbiggestsofar Then
intbiggestsofar = intNumber
End If
End If
rstMembers.MoveNext
Loop
' at this point, biggestsofar should have the largest number of any MemID with the right first letter.
Dim strNewID As String
strNewID = strLeftLetter + Format(intbiggestsofar + 1)
Me!MemID = strNewID
End Sub

 

[MarkK]

In your new database, which you can design any way you want, don't store the letter and the number in the same field. Store them raw, separate from each other, and only combine them when you need to show them to a human. Then finding your Max for the letter 'L', say, is dead simple...

DMax("IDNumber", "tMembers", "IDLetter = 'L'")

... and show your final ID with a simple expression ...

DLookup("IDLetter & IDNumber", "tMember", "MemberID = 123456")

And don't use this combination scheme, like A256, as your primary key.

 

[Beetle]

I agree with lagbolt, but if you're stuck in some scenario where you have to find a kludge for this then code like the following might get you where you want. I would suggest that you run this in the Before Update event of the form, not of the LastName control. You would also want to check for New Record (so you're not overwriting values in existing records), so I included that as well;

Dim strSQL As String, strLetter As String, strNewID As String
Dim intNewNum As Integer

If Me.NewRecord Then
    strLetter = Me![LastName]
    
    strSQL = "Select * From Members Where Left([MemID],1)=""" & strLetter & _
             """ Order By Val(Right([MemID], Len([MemID])-1));"
    
    With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If .RecordCount = 0 Then
            intNewNum = 1
        Else
            .MoveLast
            intNewNum = Val(Right(![MemID], Len(![MemID]) - 1)) + 1
        End If
    End With
    strNewID = strLetter & intNewNum

    Me![MemID] = strNewID
End If

EDIT: had the field update outside the If block in first post. Fixed.

 

[klaidlaw]

In your new database, which you can design any way you want, don't store the letter and the number in the same field. Store them raw, separate from each other, and only combine them when you need to show them to a human. Then finding your Max for the letter 'L', say, is dead simple...

DMax("IDNumber", "tMembers", "IDLetter = 'L'")

... and show your final ID with a simple expression ...

DLookup("IDLetter & IDNumber", "tMember", "MemberID = 123456")

And don't use this combination scheme, like A256, as your primary key.

Thanks. I love the idea of storing the numbers separate from the letters, but my problem is what do I do with the 3000+ IDs that are already in the database? Even if I start with a fresh number plan, I still need to start each one from the point the old set of ID numbers end. Be assured I am not going to use this as the primary key. I figured that one out immediately. I also have to get rid of the IDs that use * as the first character to indicate affiliate number. Trying to deal with a wildcard character as a regular character is a nightmare already.

 

[klaidlaw]

I agree with lagbolt, but if you're stuck in some scenario where you have to find a kludge for this then code like the following might get you where you want. I would suggest that you run this in the Before Update event of the form, not of the LastName control. You would also want to check for New Record (so you're not overwriting values in existing records), so I included that as well;

Dim strSQL As String, strLetter As String, strNewID As String
Dim intNewNum As Integer

If Me.NewRecord Then
    strLetter = Me![LastName]
    
    strSQL = "Select * From Members Where Left([MemID],1)=""" & strLetter & _
             """ Order By Val(Right([MemID], Len([MemID])-1));"
    
    With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If .RecordCount = 0 Then
            intNewNum = 1
        Else
            .MoveLast
            intNewNum = Val(Right(![MemID], Len(![MemID]) - 1)) + 1
        End If
    End With
    strNewID = strLetter & intNewNum

    Me![MemID] = strNewID
End If

EDIT: had the field update outside the If block in first post. Fixed.

Thank you. I will try the code this afternoon. At least I can see that I was in the right ballpark with what I was attempting to do. Given that I am stuck with the 3000+ IDs that are already assigned I am not going to be able to go with Lagbolt's simple but elegant solution.

 

[Beetle]

Given that I am stuck with the 3000+ IDs that are already assigned I am not going to be able to go with Lagbolt's simple but elegant solution.

Actually, If the field in question is not a PK or FK, then it wouldn't be all that difficult to use an update query so separate the existing values. Post back if you want more advice on that.

 

[klaidlaw]

Hm, when I run Beetle's code I get the following error. (I should have stated up front the ID field is set for 6 characters, but nothing in the code leads me to think the data is going to be too large for the field.) I only have two existing record in the tblMembers with MemID of L345 and C23.

"Error number 3163, the field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data. "

I can certainly drop the relationships, change the length of the field and re-establish the relationships if that is all the problem is.

 

[klaidlaw]

Actually, If the field in question is not a PK or FK, then it wouldn't be all that difficult to use an update query so separate the existing values. Post back if you want more advice on that.

I made sure I did not use the MemID as PK. It is indexed as unique, but I have a separate hidden autonumber field for the PK. When I get the new database put together and tested with fake data, I plan to append the data from the old database into the new tables.

 

[Beetle]

Try commenting out the field update line for now - the Me![MemID] = strNewID - and insert a Debug.Print strNewID or MsgBox strNewID so you can see the value that is being generated by the procedure.

 

[CJ_London]

Hi,

A faster way of determining the number to be assigned is:

Function NewMemID(strLetter as string) as String
Dim Mydb as Database
Dim Rst as Recordset

set mydb=currendb
set rst=mydb.openrecordset("Select max(val(mid(MemID,2))) From Members Where Left([MemID],1)='" & strLetter & "'") 'Note mid function does not need to specify number of chars
NewMemID=strLetter & rst.fields(0)+1
set rst=nothing
set mydb=nothing

end sub

Hope this helps

 

[klaidlaw]

Lugbolt's code worked, but instead of taking the first letter of the last name for the new ID it is taking the Last name and adding 1. So in my database I have L345 and Lee1. That is why when I typed Lawrence for the last name it gave me an error that I was trying to add to much data to a field limited to 6 characters.

 

[klaidlaw]

Hi,

A faster way of determining the number to be assigned is:

Function NewMemID(strLetter as string) as String
Dim Mydb as Database
Dim Rst as Recordset

set mydb=currentdb
set rst=mydb.openrecordset("Select max(val(mid(MemID,2))) From Members Where Left([MemID],1)='" & strLetter & "'") 'Note mid function does not need to specify number of chars
NewMemID=strLetter & rst.fields(0)+1
set rst=nothing
set mydb=nothing

end sub

Hope this helps

Thanks, but I can't get the code to work. It runs, but it doesn't update the MemID field on the form. Noted the typo in set mydb=currendb and changed it.

 

[Beetle]

Lugbolt's code worked, but instead of taking the first letter of the last name for the new ID it is taking the Last name and adding 1. So in my database I have L345 and Lee1. That is why when I typed Lawrence for the last name it gave me an error that I was trying to add to much data to a field limited to 6 characters.

I think you mean the code that I posted, which does have a typo. I have reposted it here with the corrected line in red;

Dim strSQL As String, strLetter As String, strNewID As String
Dim intNewNum As Integer

If Me.NewRecord Then
    [COLOR="Red"]strLetter = Left(Me![LastName],1)[/COLOR]
    
    strSQL = "Select * From Members Where Left([MemID],1)=""" & strLetter & _
             """ Order By Val(Right([MemID], Len([MemID])-1));"
    
    With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If .RecordCount = 0 Then
            intNewNum = 1
        Else
            .MoveLast
            intNewNum = Val(Right(![MemID], Len(![MemID]) - 1)) + 1
        End If
    End With
    strNewID = strLetter & intNewNum

    Me![MemID] = strNewID
End If

 

[CJ_London]

Try putting the following into the AfterUpdate event for your forms LastName control

Me.MemID=NewMemID(left(LastName,1))

 

[klaidlaw]

I found the problem is Beetle's code. I apologize for calling it Lugbolt's code in the earlier email. The issue was that strLetter = Me![LastName] had to be = Left(![LastName], 1)

Everything seems to be working correctly now.

I can't thank you all enough for helping with this issue. It has stopped me from getting on with all the other problems with this ancient database. You are an awesome bunch of folks. I hope I can one day repay your kindness by helping somebody else.