Extracting Text and Numbers from a CustomerID 2

[jt463]

Any ideas why I would be getting a Run-Time Error 13: "Type Mismatch" with the following code:

dim labelID2 as string

labelID2 = (Left(ContRst!CustomerID, "-"))

ContRst!CustomerID = GW-99

I am trying to store the 'GW' and '99' data in my VB.

Thanks in advance!

 

[abaldwin]

I believe that you need to replace the "-" with the location of the dash

Insert this below your current dim statement

dim iPlace as integer
iplace = Instr(ContRst!CustomerID,"-")

if iplace <> 0 then
labelid2.caption = left(contrst!customerid, iplace -1)
else
labelid2.caption = "No Dash"
end if

Instr returns an integer representing the location of the text being looked for. Then you can get the left (string function) of the string using the placement -1 (so you dont also return the dash).


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[ItIsHardToProgram]

Or you could do

labelID2 = str(left(ContRst!CustomerID, 2)) + str(Right(ContRst!CustomerID, 2))

this will return GW99

if u want to split the number and letters, seperate the 2 parts...

 

[jt463]

Thanks for your insight, Andy. I tried the suggested code, and I get: "Invalid Qualifier" on

[COLOR=red]labelid2.caption[/color red] = left(contrst!customerid, iplace -1)

Perhaps I am doing something wrong?

 

[abaldwin]

That would be assuming that there are always TWO characters, a dash and two digits.

If you want to store both parts in two different labels, variables etc you could do

dim iPlace as integer
iplace = Instr(ContRst!CustomerID,"-")

if iplace <> 0 then
labelid2.caption = left(contrst!customerid, iplace -1)
labelid3.caption = mid(contrst!customerid, iplace + 1)
else
labelid2.caption = contrst!customerid
lableid3.caption = "No Dash"
end if

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[ItIsHardToProgram]

Of course abald, but i assumed that they were only divided by 4 digit since customerIds usualy use same logic...

 

[abaldwin]

Take away the .caption part. I was assuming the control is a caption. It may be a text box. Granted most customer ID's do follow some logic, I did not mean to sound demeaning or anything. My apologies if it came out that way. I have usually found that Customer Id's that look like this are some type of identifier , a dash, and a number. More than 99 customers and you get more than two digits. If sales or management or whatever add a new type then you may wind up with more than a two character beginning identifier.

Once again. Sorry if that post sounded bad. Never intended for it to be that way.






Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[ItIsHardToProgram]

I was not offended in any way, just was explaining why I gave out this idea, either way, I would agree your idea is probably a better way, but also more difficult to understand for some one starting only reason I gave my input =/

 

[jt463]

abald - have you ever been called a "genius"? Well, you are because it worked!

I am a beginner, so I concur with ItIsHard that it was a challenge for me to follow your logic. I think I may have it, and I can't tell you both how much I appreciate your time!!!

 

[ItIsHardToProgram]

dim iPlace as integer
iplace = Instr(ContRst!CustomerID,"-")

if iplace <> 0 then
labelid2.caption = left(contrst!customerid, iplace -1)
labelid3.caption = mid(contrst!customerid, iplace + 1)
else
labelid2.caption = contrst!customerid
lableid3.caption = "No Dash"
end if

See im sure he would take the time to explain it to you but ill do it gladly, Instr is a function that returns the place in the string of the wanted caracter.

so in this code, it always returns 3 (if your custumer number is GW-99)

then it simply sends all the characters before the iplace ("-") iplace - 1 and after sends all the characters after iplace (iplace + 1) since mid returns all the caracters starting from the position mentioned.

hope that clears things up for you

 

[abaldwin]

Not a genius but thanks for the thought.

Thanks Hard for the explanation.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[PHV]

Another way (ac2k or above):
Dim labelID2 As String, labelID3 As String, myArr
myArr = Split(ContRst!CustomerID, "-")
labelID2 = myArr(0)
If UBound(myArr) > 0 Then
labelID3 = myArr(1)
Else
labelID3 = "No Dash"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[abaldwin]

Yea, The array approach is the more efficient way. Dont know why I did not post that. mmmmmm guess I am loosing it in my old age.

Laterdays


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[jt463]

Okay, I am not familiar with the array approach, but I can see that would be valuable for me to learn it.

In the mean time, I have taken abald's tip and have attempted to accomplish what I sent out at the beginning to achieve, and I have run in to a problem.

Originally, I wanted to extract the number portion of the ID because I am having problems when I try to add a new customer ID after GW-100. When I do a 'Select' on all Customer IDs with 'GW' in them and then sort them so I can find what the last one is and then add '1' to it. For some reason Access (or VB) doesn't sort the way I would like for it to sort. GW-100 ends up at the top of my order and it keeps adding '1' to GW-99 and getting GW-100. So, I keep getting multiple GW-100 IDs. Not good.

So, I opened this thread to see if there was a way in VB to extract the GW and the 100 separately. Thanks to your help, I found that I can.

I then tried utilizing that bit of code in a 'Select' statement, and it is blowing up:

Set numdb = CurrentDb
Set numrst = numdb.OpenRecordset("select Mid([CustomerID], iPlace + 1) As CustID from Contacts WHERE CustomerID Like '" & labelID2 & "*' ORDER BY CustID")

It is saying 'too few parameters'. I hope to get from that statement the numbers 1 through 100 so that I can simply add '1' and make the next record '101'!

What a mess! I figure there has got to be an easier way. The reason I need to do the CustomerID like this is that I have several different users who each need to query on their own 'GW' value, so I need to keep my customers in groups but also need to add them incrementally.

Help!

 

[PHV]

You may try something like this:
nextID = 1 + Nz(DMax("Val(Mid(CustomerID," & iPlace + 1 &))", "Contacts", "CustomerID Like '" & labelID2 & "-*'"), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jt463]

Thanks PHV. I am vaguely familiar with the Nz function. However, does your suggestion somehow order the records? My problem is that select automatically puts the results into an ordered format. The order unfortunately ends up something like this:

GW-1
GW-10
GW-100
GW-11
GW-12
GW-13
GW-14...
...GW-19
GW-2
GW-20...etc...

So, I am not able to tell the code that GW-100 is the last one that needs to have the '1' added to it.

Perhaps what you are suggesting will accomplish this. If so, that would be huge! I just am not familiar with what each of those options provides.

Thanks again in advance!

 

[jt463]

I tried inserting the suggestion from PHV like this:

nextID = 1 + Nz(DMax("Val(Mid(ContRst!CustomerID," & iPlace + 1 & "))", "Contacts", "ContRst!CustomerID Like '" & labelID2 & "-*'"), 0)

It tells me I have: "Cancelled the Previous Operation.

 

[jt463]

When I tried the original suggestion, I get a Compile Error: Expected Expression.

nextID = 1 + Nz(DMax("Val(Mid(ContRst!CustomerID," & iPlace + 1 & [b][COLOR=red])[/color red][/b])", "Contacts", "ContRst!CustomerID Like '" & labelID2 & "-*'"), 0)

 

[PHV]

and this ?
nextID = 1 + Nz(DMax("Val(Mid(CustomerID," & iPlace + 1 & "))", "Contacts", "CustomerID Like '" & labelID2 & "-*'"), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jt463]

I ran a quick test with a message box, and...IT WORKED!!!

I think I see that you moved the quotes around. I believe that is correct.

I appreciate your time, PHV...little by little I am picking up on this. I appreciate the time and patience you and the other experts on the forum provide.