Gasman
Enthusiastic Amateur
- Local time
- Today, 18:36
- Joined
- Sep 21, 2011
- Messages
- 16,442
Yes, it is, so you need to start at character 8?not sure how you get 8 to work as "ZS-RPB(" is 7 characters

Yes, it is, so you need to start at character 8?not sure how you get 8 to work as "ZS-RPB(" is 7 characters
I created a query where i renamed the control to just DAW, in the second query i used the DAW as source string and not the source from SAPIn 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.
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.
It makes no difference using 7 or 8Yes, it is, so you need to start at character 8?![]()
When you use the Mid function e.g, Mid(your string, 7,5), the result displays 5 characters starting at the 7th characternot 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
I'm more concerned as to why it is not respecting the length to extract?When you use the Mid function e.g, Mid(your string, 7,5), the result displays 5 characters starting at the 7th character
ID | DAW |
---|---|
1 | ZS-RPB (30551)Sliding door handle mechani |
2 | ZS-RPB (30551) Sliding door handle mechani |
4 | ZS-RPBXXX (30551AAAA)Sliding door handle mechani and more text |
5 | ZS-RPB234 (30551)Sliding door handle mechani |
SELECT
tblSAP.DAW,
GetPart([DAW],1) AS Registration,
GetPart([DAW],2) AS Doc_Number,
GetPart([DAW],3) AS Description
FROM tblSAP;
DAW | Registration | Doc_Number | Description |
---|---|---|---|
ZS-RPB (30551)Sliding door handle mechani | ZS-RPB | 30551 | Sliding door handle mechani |
ZS-RPB (30551) Sliding door handle mechani | ZS-RPB | 30551 | Sliding door handle mechani |
ZS-RPBXXX (30551AAAA)Sliding door handle mechani and more text | ZS-RPBXXX | 30551AAAA | Sliding door handle mechani and more text |
ZS-RPB234 (30551)Sliding door handle mechani | ZS-RPB234 | 30551 | Sliding door handle mechani |
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
GetPart = Trim(GetPart)
End Function
There is nothing wrong with the mid function, I am sure of that. It is clearly user error. It has been two pages of the OP unable to count the correct number of characters. I seen 9,5; 75, and 7,5. Fails if any space is there.However what happens when they need to use the Mid() function somewhere else.?
I was trying to be diplomatic. Not my strong point I admit.There is nothing wrong with the mid function, I am sure of that. It is clearly user error. It has been two pages of the OP unable to count the correct number of characters. I seen 9,5; 75, and 7,5. Fails if any space is there.
Simple Google search returns no other instances of mid function returning wrong characters, so I highly doubt they have an instance where it is not working.
Much better than me. I tend to revert to the "tough love" approach. When I see people say "the function stopped working or stopped working properly", 99.9% of the time it is a change in the input data. Unless there is corruption.I was trying to be diplomatic. Not my strong point I admit.