Mid Function (1 Viewer)

Gismo

Registered User.
Local time
Today, 07:19
Joined
Jun 12, 2017
Messages
1,298
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.
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 SAP
result is still not as required
String starts of correct at the 30551 but does not cut off at position 5
 

Gismo

Registered User.
Local time
Today, 07:19
Joined
Jun 12, 2017
Messages
1,298
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.
1587024222176.png
 

Gismo

Registered User.
Local time
Today, 07:19
Joined
Jun 12, 2017
Messages
1,298
Yes, it is, so you need to start at character 8? :mad:
It makes no difference using 7 or 8
starting point is not the problem, it is cutting off after the number, before the comma
1587024306943.png
1587024355454.png
 
Last edited:

jocph

Member
Local time
Today, 13:19
Joined
Sep 12, 2014
Messages
61
Try this:

Daw_No: Right(Left([DAW],12),5)

I get this from the immediate window;
Code:
?Right(Left("ZS-RPB(30551)Sliding door handle mechani",12),5)
30551
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:19
Joined
Sep 21, 2011
Messages
14,048
Have you tried MajP's function then?

Make up some string or use that data in a string and test MID() in the immediate window and post back the results.
 

isladogs

MVP / VIP
Local time
Today, 05:19
Joined
Jan 14, 2017
Messages
18,186
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
When you use the Mid function e.g, Mid(your string, 7,5), the result displays 5 characters starting at the 7th character
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:19
Joined
Sep 21, 2011
Messages
14,048
When you use the Mid function e.g, Mid(your string, 7,5), the result displays 5 characters starting at the 7th character
I'm more concerned as to why it is not respecting the length to extract? :unsure:
 

isladogs

MVP / VIP
Local time
Today, 05:19
Joined
Jan 14, 2017
Messages
18,186
Yes I agree. Time for the OP to try another approach or upload a sample dB.
 

moke123

AWF VIP
Local time
Today, 01:19
Joined
Jan 11, 2013
Messages
3,852
just my 2¢
Thinking long term, I would think MajP's function would be less likely to need tweeking should there be any changes in the lengths of the elements.
(ie. 30551A or 305512 ) I tend to think element lengths would be more likely to change before the the format would.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:19
Joined
May 21, 2018
Messages
8,463
Not sure how long you are going to keep banging your heads, but just saying....
tblSAP

IDDAW
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
Code:
SELECT
  tblSAP.DAW,
  GetPart([DAW],1) AS Registration,
  GetPart([DAW],2) AS Doc_Number,
  GetPart([DAW],3) AS Description
FROM tblSAP;
Query1

DAWRegistrationDoc_NumberDescription
ZS-RPB (30551)Sliding door handle mechaniZS-RPB30551Sliding door handle mechani
ZS-RPB (30551) Sliding door handle mechaniZS-RPB30551Sliding door handle mechani
ZS-RPBXXX (30551AAAA)Sliding door handle mechani and more textZS-RPBXXX30551AAAASliding door handle mechani and more text
ZS-RPB234 (30551)Sliding door handle mechaniZS-RPB23430551Sliding door handle mechani

Added a trim function to the end to handle cases like 4
Code:
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
 

jocph

Member
Local time
Today, 13:19
Joined
Sep 12, 2014
Messages
61
I agree that MajP's solution would be best. No time like the present to learn custom functions.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:19
Joined
Sep 21, 2011
Messages
14,048
Well if the O/P cannot get a simple MID() function to work for some strange reason at present, they might have to use MajP's function.

However what happens when they need to use the Mid() function somewhere else.?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:19
Joined
May 21, 2018
Messages
8,463
However what happens when they need to use the Mid() function somewhere else.?
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:19
Joined
Sep 21, 2011
Messages
14,048
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.
I was trying to be diplomatic. Not my strong point I admit. :)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:19
Joined
May 21, 2018
Messages
8,463
I was trying to be diplomatic. Not my strong point I admit.
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.
 

Users who are viewing this thread

Top Bottom