• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Mid Function (1 Viewer)

Gismo

Registered User.
Local time
Today, 18:46
Joined
Jun 12, 2017
Messages
574
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, 18:46
Joined
Jun 12, 2017
Messages
574
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, 18:46
Joined
Jun 12, 2017
Messages
574
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, 23:46
Joined
Sep 12, 2014
Messages
52
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, 16:46
Joined
Sep 21, 2011
Messages
6,760
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

CID VIP
Local time
Today, 16:46
Joined
Jan 14, 2017
Messages
13,917
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, 16:46
Joined
Sep 21, 2011
Messages
6,760
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

CID VIP
Local time
Today, 16:46
Joined
Jan 14, 2017
Messages
13,917
Yes I agree. Time for the OP to try another approach or upload a sample dB.
 

moke123

AWF VIP
Local time
Today, 11:46
Joined
Jan 11, 2013
Messages
1,674
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, 11:46
Joined
May 21, 2018
Messages
3,480
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, 23:46
Joined
Sep 12, 2014
Messages
52
I agree that MajP's solution would be best. No time like the present to learn custom functions.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:46
Joined
Sep 21, 2011
Messages
6,760
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, 11:46
Joined
May 21, 2018
Messages
3,480
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, 16:46
Joined
Sep 21, 2011
Messages
6,760
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, 11:46
Joined
May 21, 2018
Messages
3,480
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