Help With Seperating String After Character (1 Viewer)

[Shaunk23]

I have deposit tickets that are formatted like this.. 060112-1 and will increase the last digit as tickets are entered for that day... So if there were 11 tickets on 6/1/12 they would be listed as below..

060112-1
060112-2
060112-3
060112-4
060112-5
060112-6
060112-7
060112-8
060112-9
060112-10
060112-11

For ordering purposes i am sorting by Deposit Ticket iD as above. The problem is it 10 is sorted right after one.. for instance my sort in listbox looks like this..

060112-1
060112-10
060112-11
060112-2
060112-3
060112-4
060112-5
060112-6
060112-7
060112-8
060112-9


My solution was to pull the "subID" which would be the last digit after "-" for sorting.. I created a function but cant seem to get it to work. Im unfamilar with this method.. Help!!

Function:

Public Function GetSubTicketID(CRID As String) As String
Dim TmpID As String
Dim SUBID As String

TmpID = CRID

SUBID = InStr(1, TmpID, "-")

Debug.Print TmpID
Debug.Print SUBID

End Function

IMMEDIATE WINDOW:

INPUT -?GetSubTicketID("052912-9")

Debug -
052912-9
7

Its getting the string correct.. But pulling a 7.. that should be a 9.

 

[Brianwarnock]

Instr returns the position of the character being tested for, you need to use that result in a mod to get what you want

Mod(tmpid,Instr(1,tmpid,"-")+1)

This selects all of the characters in the string starting after the -

Brian

 

[Shaunk23]

Hi Brian - It throws error On "MOD" im using access - vb . I Believe i got it using the below

Public Function GetSubTicketID(CRID As String) As String
Dim TmpID As String
Dim SUBID As Variant

TmpID = CRID

Dim i As Integer

For i = Len(TmpID) To 1 Step -1
If Mid(TmpID, i, 1) = "-" Then
SUBID = Mid(TmpID, i + 1, Len(TmpID) + i)
Exit For
End If
Next i

'Debug.Print TmpID
'Debug.Print SUBID


End Function

 

[Brianwarnock]

Mod was a typo should have been mid, actuall I'm on an iPad and it was probably auto corrected!! And I did not notice.

Brian

Ps you don't need a length in a mid if you want the rest of the string

 

[Shaunk23]

Haha - thanks man

 

[Brianwarnock]

Your welcome, the iPad is good for something's and when I find out what I will post, but my pc is ancient and slow.

Brian