Get values after delimiter in VB6

ok... that worked fine until invoice number 10, but after 10, it won't go up anymore.. how am i suppose to get around this problem? is it because i store the data in my database as varchar?

What is the size of the field in the database? I could be the field is too small to accommodate strings that a longer than 20 characters.

What error are you getting?

Alternative without using Split:

Option Explicit
 
Private Sub Form_Load()
   Dim s                As String
   Dim i                As Integer
 
   s = "JEUSC001-1/CEI000001"
   For i = 1 To 20
      s = IncrementCounter(s)
      Debug.Print s
   Next
End Sub
 
Function IncrementCounter(ByVal sIn As String) As String
   Dim iPos As Integer
   Dim jPos As Integer
   
   iPos = InStr(1, sIn, "-")
   jPos = InStr(iPos, sIn, "/")
 
   IncrementCounter = Left(sIn, iPos) & (1 + CInt(Mid(sIn, iPos + 1, jPos - iPos - 1))) & Mid(sIn, jPos)
End Function

Select allOpen in new window

The size for the field in my database is 30 and its suppose to store Varchar. I didn't get any error messages but whenever i generate the invoice number, i keep getting JEUSC001-10 and couldn't get beyond that. If i print the i, it always returned me 9. Is it because i store it as varchar?

i think ur code will limit the length to be 20 right? but if the invoice number is to be like this:
JEUSC001-10/CEI000001, it's going to be a problem right?

It will return the Invoice Number correctly between characters "-" and "/" regardless of how many character the string is.

errmm... sorry to bother u again mr.danaseaman, but when i run it, it gave me the output of JEUSC001-21/CEI00000121.... I only want the digit behind SC001 to increment, and it's supposed to be JEUSC001-2/CEI000001... any idea on how to do it?

The demo shows both functions. Just call Function InvoiceNumber with your string by itself and you will get the correct InvoiceNumber.

and when i put in JEUSC001-10/CEI000001, when i run ur code, it gave me the output of JEUSC001-30/CEI00000130. It's weird...

Function InvoiceNumber returns a Long. What are you doing to return a string?

   s = "JEUSC001-1/CEI000001"
   For i = 1 To 20
      s = IncrementCounter(s)
      Debug.Print InvoiceNumber(s)
   Next

my invoice number of JEUSC001-1/CEI000001 consists of JEU -> Code for selling to customer(end user), SC001 -> supplier code - 1-> is the invoice number. /CEI000001 -> Customer Code. Now i take the invoice number from my database by using this query....
select * from salecustomer where substr(salecust_id, 4,5) = '" & cmbsc & "'", ac, adOpenDynamic, adLockOptimistic. Then i do a .movelast move to get to the very last invoice number entered for that particular supplier. cmbsc is a combo box where the supplier code is contained. After i performed all the calculation for the invoice number, then i add in the customer code lastly, taken from a customer code label in the VB form.... hope this clears things up as whenever i query my database table, the last number for a particular supplier is 9 (ie. JEUSC001-9/CEI000001). The invoice JEUSC001-10/CEI000001 appeared below JEUSC001-1/CEI000001 and before JEUSC001-2/CEI000001. Is it because i store it as a varchar?? any idea of how to get around this problem?

currently, my incoice number format is JEUSC001-001/CEI000001 and it worked fine and i can do the increment thing ok in this mode... However, in this mode, i can only go up to 999 and no more for each supplier... Thats why i want to change to a new invoice number format so that it'll be more flexible...

If your query is returning the data:
JEUSC001-1/CEI000001
JEUSC001-10/CEI000001
JEUSC001-2/CEI000001

Then .MoveLast is not going to get the highest Invoice Number since the data appears to be sorted by string.
Do you have a date field that you could use to sort? I'm guessing that .MoveLast would then get the highest or most recent Invoice Number.

alright thats a great idea... i created another column to store those invoice number which go up incrementally and i do a group by with my sql and it worked now... it goes up to 1000 and more... thanks experts =)