Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Get values after delimiter in VB6

Dear Experts,
Lets say I have an invoice number in JEUSC001-1/CEI000001 format. I'm trying to get this invoice number to increment its value. The value i want it to increase is the number 1 after the - sign. I tried using the split function but i cant get the value 1 for me to mess around with. Help please? Thank you
0
Slythie
Asked:
Slythie
  • 8
  • 6
  • 2
2 Solutions
 
bmatumburaCommented:

Dim s As String, result As String
Dim i As Integer
 
s="JEUSC001-1/CEI000001"
i=CInt(Split(Split(s,"-")(1), "/")(0))
i = i + 1
 
result = Split(s, "-")(0) + "-" + CStr(i) + "/" + Split(s, "/")(1)

Open in new window

0
 
SlythieAuthor Commented:
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?
0
 
bmatumburaCommented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Open in new window

0
 
SlythieAuthor Commented:
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?
0
 
danaseamanCommented:
Try this code to retrieve the Invoice Number from string.
 

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, InvoiceNumber(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
 
Function InvoiceNumber(ByVal sIn As String) As Long
   Dim iPos As Integer
   Dim jPos As Integer
   
   iPos = InStr(1, sIn, "-")
   jPos = InStr(iPos, sIn, "/")
 
   InvoiceNumber = CInt(Mid(sIn, iPos + 1, jPos - iPos - 1))
End Function

Open in new window

0
 
SlythieAuthor Commented:
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?
0
 
danaseamanCommented:
It will return the Invoice Number correctly between characters "-" and "/" regardless of how many character the string is.
0
 
SlythieAuthor Commented:
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?
0
 
danaseamanCommented:
The demo shows both functions. Just call Function InvoiceNumber with your string by itself and you will get the correct InvoiceNumber.
0
 
SlythieAuthor Commented:
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...
0
 
danaseamanCommented:
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
0
 
SlythieAuthor Commented:
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?
0
 
SlythieAuthor Commented:
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...
0
 
danaseamanCommented:
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.


0
 
SlythieAuthor Commented:
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 =)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now