Sort numeric in alpha-numeric text (runtime error 3464) Data type mismatch criteria

[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:

Set rst = dbs.OpenRecordset("SELECT Max(CLng(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 & "1001"
End If

My problem is that code is giving runtime error 3464 Data type mismatch in criteria.

I am trying to resolve but unable :banghead:

Thanking you all in advance
Muaz

 

[gemma-the-husky]

in your query, you need another column which extracts the number from the code - then you can sort on the number part.

there isn't a simple function though - val() nearly does it, but val only works on text strings starting with a number

val("123-test") is 123 - but val("test-123") is just 0

----

depending how far developed you are, another solution might be to separate the prefix and the number into 2 separate fields.

 

[Brianwarnock]

If the entries are of the format text - numeric one can use the usual text parsing functions of InstrRev Len,right mid etc to extract the number and Cint to convert the string to a number

Brian

 

[Muaz]

Thank you all for your support. Issue Resolved. I use the following logic to solve this problem.

Set rst = dbs.OpenRecordset("SELECT Max(Val(Mid([nBadge_Num],InstrRev([nBadge_Num],'-')+1,4))) 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 & "1001"
End If

Thank you all once again.

Best Regards
Muaz