Mid Function (1 Viewer)

Gismo

Registered User.
Local time
Today, 19:32
Joined
Jun 12, 2017
Messages
1,298
Hi All,

i am importing data from our system, the field i am using is a combination of data, such as registration number, document number and description
I want to separate the fields data in to usable data.
I managed to extract the Registration
the next data i need is the document number. I used below code, it start is correct but it does not cut off as required, it displays all the data after the required cut off point
any suggestions?
the query contains 3 tables
Sample Data: ZS-RPB(30551)Sliding door handle mechani
Registration is "ZS-RPB"
Document number is all between the brackets: 30551
After Bracket is the description

DAW No: Mid([IW49 - Line Items TBL]![Opr# short text],7,5)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:32
Joined
Oct 29, 2018
Messages
21,358
Hi. Mid() should work. Just posting another possible approach: RegEx. Cheers!

 

onur_can

Active member
Local time
Today, 10:32
Joined
Oct 4, 2015
Messages
180
Code:
Dim result as String
Result = Mid ("sampletext",9,5)

will provide the document number in your sample data.
 

Gismo

Registered User.
Local time
Today, 19:32
Joined
Jun 12, 2017
Messages
1,298
Code:
Dim result as String
Result = Mid ("sampletext",9,5)

will provide the document number in your sample data.
That is what though but this s the result

30551)Sliding door handle mechani

i only need 30551

ZS-RPB(30551)Sliding door handle mechani
DAW No: Mid([IW49 - Line Items TBL]![Opr# short text],7,5)
 
Last edited:

onur_can

Active member
Local time
Today, 10:32
Joined
Oct 4, 2015
Messages
180
ZS-RPB(30551)Sliding door handle mechani
Code:
MID ("ZS-RPB(30551)Sliding door handle mechani",9,5)

when we count from the beginning 9th character 3
We get 5 characters from 3.
Conclusion What do you think happens?
Wouldn't it be 30551?

you are doing wrong somewhere.

If you don't write the 2nd parameter, it takes all after the 9th character.
 

onur_can

Active member
Local time
Today, 10:32
Joined
Oct 4, 2015
Messages
180
[QUOTE = "Gismo, gönderi: 1685878, üye: 144535"]
DAW No: Mid ([IW49 - Satır Öğeleri TBL]! [Opr # kısa metin], 7 5)
[/ALINTI]

Now you caught my attention you didn't add commas between 7 and 5
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:32
Joined
Feb 28, 2001
Messages
26,999
You should avoid having special characters in names. Spaces are not helpful either.

Starting from "ZS-RPB(30551)Sliding door handle mechani" (which I will assume to be in a a string named OKN) and presuming that you are really looking only for the number inside the parentheses:

Code:
Mid( [OKN], 
    Instr( 1, [OKN], "(" ) + 1, 
    ( Instr( 1, [OKN], ")" ) - 1 ) - Instr( 1, [OKN], "(" ) )

or something close to this.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:32
Joined
Sep 21, 2011
Messages
14,044
I see 8 for 5 characters :unsure:
 

onur_can

Active member
Local time
Today, 10:32
Joined
Oct 4, 2015
Messages
180
Guys, there is no need to write such long things that the problem is obvious and the problem is solved very well with the code I suggest. It ends with just one line. No need to use anything other than MID function. But I assume the length is the same until the 9th character.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:32
Joined
May 21, 2018
Messages
8,463
I will assume registration is not always x characters, but the doc number is always enclosed.
Code:
Public Enum DataPart
Reg = 1
DocNum = 2
Desc = 3
End Enum

'Sample Data: ZS-RPB(30551)Sliding door handle mechani
'Registration is "ZS-RPB"
'Document number is all between the brackets: 30551
'After Bracket is the description

Public Function GetPart(Data As String, PartName As DataPart) As String
  Select Case PartName
   Case Reg
     GetPart = Split(Data, "(")(0)
   Case DocNum
     GetPart = Split(Data, "(")(1)
     GetPart = Split(GetPart, ")")(0)
   Case Desc
     GetPart = Split(Data, ")")(1)
  End Select
End Function

Public Sub TestIt()
  Dim x As String
  x = "ZS-RPB(30551)Sliding door handle mechanism"
  Debug.Print GetPart(x, Reg)
  Debug.Print GetPart(x, DocNum)
  Debug.Print GetPart(x, Desc)
End Sub

ZS-RPB
30551
Sliding door handle mechanism
 

Micron

AWF VIP
Local time
Today, 13:32
Joined
Oct 20, 2018
Messages
3,476
Guys, there is no need to write such long things that the problem is obvious and the problem is solved very well with the code I suggest.
I dunno. I count and the 9th character is 0.
1. Z
2. S
3. -
4. R
5. P
6. B
7. (
8. 3
9. 0
10, 11, 12, 13. 551)
Besides even if I was wrong about that, what's it to you if anyone wants to suggest alternatives? It gives the OP options, and in this case, maybe more accurate information as well. Don't get the idea that the first correct answer wins, OK? It doesn't work like that here.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:32
Joined
May 21, 2018
Messages
8,463
Further it is also an extremely limited solution. It works for one example case.
Registration has to be six characters, and number has to be 5 digits. Without seeing the data I would not assume that.
At least the solution I propose works for any length registration and any length document number. Probably far more likely. Still assumes format is
registration(docnumber)description.
 

onur_can

Active member
Local time
Today, 10:32
Joined
Oct 4, 2015
Messages
180
Sample Data: ZS-RPB(30551)Sliding door handle mechani
Sample Data: ZS-RPB (30551)Sliding door handle mechani

i am importing data from our system, the field i am using is a combination of data, such as registration number, document number and description
I want to separate the fields data in to usable data.
I managed to extract the Registration
the next data i need is the document number. I used below code, it start is correct but it does not cut off as required, it displays all the data after the required cut off point
any suggestions?
the query contains 3 tables
Sample Data: ZS-RPB(30551)Sliding door handle mechani
Registration is "ZS-RPB"
Document number is all between the brackets: 30551
After Bracket is the description

Have you analyzed the problem thoroughly? I said the 9th character since I saw an extra space in front of the document number. It's true what you say. It will start from the 8th character. Gismo, who conveyed the problem to us, says that he takes it all from the starting character. What does this mean that he does not see the value 5, which is the 3rd parameter of the MID function. So what could be the reasons for you? The 3rd parameter is not evaluated. If you want to get exact results, you can add a small sample of the database.
 

Gismo

Registered User.
Local time
Today, 19:32
Joined
Jun 12, 2017
Messages
1,298
Hi All,

just to reply on all your comments

With regards to the special characters, this is the format it is downloaded from SAP
the format of the Registration and the document number is always in the same format XX-XXX(00000)

i used the Mid function in a separate query where the output was not as required but it did start off with the document number as it should
I then used a second query this to see that happens, I used the Left Function on the output of the first query and the result was just the Document number as required
so not sure where i went wrong with the mid function, i used different margins on the start and the length
 

onur_can

Active member
Local time
Today, 10:32
Joined
Oct 4, 2015
Messages
180
What was the result? Were you able to fix it?
 

jocph

Member
Local time
Tomorrow, 01:32
Joined
Sep 12, 2014
Messages
61
That is what though but this s the result

30551)Sliding door handle mechani

i only need 30551

ZS-RPB(30551)Sliding door handle mechani
DAW No: Mid([IW49 - Line Items TBL]![Opr# short text],7,5)

Using the immediate window, this is what I get:

Code:
?Mid("ZS-RPB(30551)Sliding door handle mechani",7,5)
(3055

?Mid("ZS-RPB(30551)Sliding door handle mechani",8,5)
30551
 

Gismo

Registered User.
Local time
Today, 19:32
Joined
Jun 12, 2017
Messages
1,298
Using the immediate window, this is what I get:

Code:
?Mid("ZS-RPB(30551)Sliding door handle mechani",7,5)
(3055

?Mid("ZS-RPB(30551)Sliding door handle mechani",8,5)
30551
not sure how you get 8 to work as "ZS-RPB(" is 7 characters
when i use 7 or 8 my string begins at the start of the document number 30551
using 7 or 8 makes no difference on my side, not sure why
and the ,5 has absolutely no effect
 

jocph

Member
Local time
Tomorrow, 01:32
Joined
Sep 12, 2014
Messages
61
In your code, on the line before this:

Code:
Mid([IW49 - Line Items TBL]![Opr# short text],7,5)


Add a Debug.Print so you would see the string input to the Mid function, like this:

Code:
Debug.Print [IW49 - Line Items TBL]![Opr# short text]

It might be the reason you are having trouble.
 

Users who are viewing this thread

Top Bottom