Sort numeric in alpha-numeric text (1 Viewer)

[Muaz]

Hi All;

I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.

The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.

Code for automatic number generation is:


Dim dbs As Database, rst As Recordset, Response

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
& "FROM newMain " _
& "HAVING InStr(1,[nBadge_Num],'" & Me.NBadge_Num & "')=1")
If Not IsNull(rst![Maxno]) Then
Me.NBadge_Num = Me.NBadge_Num & rst![Maxno] + 1
Else
Me.NBadge_Num = Me.NBadge_Num & "0001"
End If

My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?

Regards
Muaz

 

[Galaxiom]

"SELECT Max([COLOR="Blue"]CLng[/COLOR](Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")[COLOR="Blue"])[/COLOR]) AS MaxNo "

 

[Muaz]

Issue Resolved.

Thank you very much for your HELP.

Best Regards
Muaz