Parse an Alphanumeric field to separate

[hpl2001]

Hi:

I have a field that has values such as : 12, 12A, 123B.... That field is an alphanumeric, and is also part of the key. I have one report that need to be sorted numerically by the data in this field. The only way I can think to do this is to create 2 new fields to hold the number and the letter separately (there will not always, or even often, be a letter). Then I will sort the table by the number field then the letter field to get the desired result.

So my question is...how to write code to take that alphanumeric entry, and split it into the number and the letter to store them in the new fields?

Tia.

Holly

 

[dhookom]

This expression will return the number:

Val("123V")

This expression will return the text

Mid("123V",Len(str(Val("123V"))))

Duane
Hook'D on Access
MS Access MVP

 

[hpl2001]

Okay, so here's how I've tried to implement this, but I am getting error 2465 on my 2 new lines. The rest of the code is pre-existing and works on its own:
------------
Select Case Ans
Case vbYes
If (IsNull(Me.Year) Or IsNull(Me.Box_Number) Or IsNull(Me.Schedule_Number) Or IsNull(Me.Facility) Or IsNull(Me.Transfer_Number)) Then
MsgBox ("Year, Box Number, Facility, Schedule Number and Transfer Number are required"), vbOKOnly
Exit Sub
Else
[Last Update] = Now()
[User Name] = Environ("Username")
NEW LINE==> [Box Numeric] = Val(Me.Box_Number)
NEW LINE==> [Box Alpha] = Mid(Me.Box_Number, Len(Str(Val(Me.Box_Number))))
MsgBox ("Box Saved"), vbOKOnly
End If
Case vbNo
Me.Undo
Case vbCancel
Cancel = True
Exit Sub
End Select
----------------

Am I making some sort of syntax error?

 

[hpl2001]

Got it...