UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Instr With Mid Function To Look At Text - Invalid Procedure Call, Access 2016    
 
   
msfarrar
post Jul 15 2019, 04:59 PM
Post#1



Posts: 97
Joined: 11-July 12



Hello,

I have a text field that is formatted the same way which is a prefix_issue1_issue2. I have successfully made queries that look at the prefix and look at issue 2. I have not been able to make the middle of the text show up. I am running a total query, so grouping everything by issue 1.

I ran this test in the immediate window of vba, and successfully get the response "testing"
CODE
Mid("test_testing_test2",InStr(1,"test_testing_test2","_")+1,CLng(InStr(6,"test_testing_test2","_")-InStr(1,"test_testing_test2","_")-1))


When I take that same formula and replace the string with my text field [short text], I get an error that says "Invalid Procedure Call"

I can look at everything but the prefix if I use this code, but doesn't let me group by issue 1.
CODE
Mid("test_testing_test2",InStr(1,"test_testing_test2","_")+1,CLng(len("test_testing_test2")-InStr(1,"test_testing_test2","_"))


Any thoughts on why the query doesn't let me look at the middle of my string.
Go to the top of the page
 
June7
post Jul 15 2019, 05:13 PM
Post#2



Posts: 730
Joined: 25-January 16



Mid(Left(x, InStrRev(x,"_")-1), Instr(x,"_")+1)

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
msfarrar
post Jul 15 2019, 05:30 PM
Post#3



Posts: 97
Joined: 11-July 12



I get the same invalid procedure call error when I use that formula.

CODE
Mid(Left(x,InStrRev(x,"_")-1),InStr(x,"_")+1)
Go to the top of the page
 
ADezii
post Jul 15 2019, 06:19 PM
Post#4



Posts: 2,541
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Simplified Table Structure:
    TestString
    test_Issue1_test2
    test_YaDa_test2
    test_MiddleText_test2
    test_YaDa_test2
    test_YaDa_test2
    test_MiddleText_test2
    test_MiddleText_test2
    test_MiddleText_test2
    test_MiddleText_test2
    test_MiddleText_test2
    test_Issue1_test2
  2. Totals Query Definition:
    SQL
    SELECT Count(tblTest.TestString) AS CountOfTestString, Mid([TestString],InStr(1,[TestString],"_")+1,CLng(InStr(6,[TestString],"_")-InStr(1,[TestString],"_")-1)) AS Result
    FROM tblTest GROUP BY Mid([TestString],InStr(1,[TestString],"_")+1,CLng(InStr(6,[TestString],"_")-InStr(1,[TestString],"_")-1));
  3. Query OUTPUT:
    CountOfTestStringResult
    2Issue1
    6MiddleText
    3YaDa
  4. What am I missing?
Go to the top of the page
 
msfarrar
post Jul 15 2019, 06:24 PM
Post#5



Posts: 97
Joined: 11-July 12



I am trying to build this into a query, but I had not gotten to the point of doing SQL instead of query builder. That may be my work around, thanks for helping me think outside the box!
Go to the top of the page
 
June7
post Jul 15 2019, 07:04 PM
Post#6



Posts: 730
Joined: 25-January 16



The expression works for me. Certainly have to substitute x with your field name.


This post has been edited by June7: Jul 15 2019, 07:07 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
cheekybuddha
post Jul 15 2019, 08:37 PM
Post#7


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


If any of your short text fields is empty (zero-length) or NULL then you will receive the error.

With this expression:
CODE
Mid("test_testing_test2",InStr(1,"test_testing_test2","_")+1,CLng(InStr(6,"test_testing_test2","_")-InStr(1,"test_testing_test2","_")-1))

look at the expression that provides the length to capture with Mid():
CODE
CLng(len("test_testing_test2")-InStr(1,"test_testing_test2","_")-1)

With an empty string you get:
CODE
?CLng(InStr(6,"","_")-InStr(1,"","_")-1)
-1

The length argument must be >= 0

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
strive4peace
post Jul 16 2019, 12:54 AM
Post#8


strive4peace
Posts: 20,447
Joined: 10-January 04



here is a function you can use to pull the middle of the string using something like:
CODE
   GetNthPart( [TestString], 2, "_")


CODE
'*************** Code Start *****************************************************
' Purpose  : Get the Nth Part in a string
' Return   : Variant (Null) or String
'--------------------------------------------------------------------------------
'                              GetNthPart
'--------------------------------------------------------------------------------
Function GetNthPart( pvString As Variant _
   , piPart As Integer  _
   , Optional psDeli As String = "-" _
   ) As Variant
' strive4peace
'  RETURNS a specified part # of a string
   'PARAMETERS
   '  pvString = string to evaluate
   '  piPart = part number to return
   '  psDeli = delimiter between parts
   ' ?Get_NthPart("abc-def-ghi-jkl",3,"-")="ghi"
  
   On Error Resume Next  
   'initialize return value to be Null
   GetNthPart = Null  
   'subtract 1 from piPart since array index starts with 0
   GetNthPart = Split(pvString, psDeli)(piPart - 1)
End Function
'*************** Code End *******************************************************

CODE
'--------------------------------------------------------------------------------
'                              test_GetNthPart
'--------------------------------------------------------------------------------
Sub test_GetNthPart ()
   'click HERE and press F5 to run
   'customize this example if you wish
   MsgBox GetNthPart("test_MiddleText_test2", 2, "_") _
      , , "test GetNthPart"
End Sub

This post has been edited by strive4peace: Jul 16 2019, 01:16 AM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
cheekybuddha
post Jul 16 2019, 03:05 AM
Post#9


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Great to see you back about the boards, Crystal! wavehi.gif

Long, long time no see!

Straight back in as if you never were away! yayhandclap.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
dale.fye
post Jul 16 2019, 05:56 AM
Post#10



Posts: 137
Joined: 28-March 18
From: Virginia


I use a function similar to Crystal's for this purpose. In your case, where you only have 3 segments in your string, it is simple to get segments 1, 2, and 3.

but to get the output you are looking for in your example, you are going to have to do something like:

CODE
SELECT Count(uq.Position), uq.NthPart
FROM (
SELECT 1 as Position, GetNthPart( [TestString], 1, "_") as NthPart
FROM tblTest WHERE GetNthPart( [TestString], 1, "_") IS NOT NULL
UNION
SELECT 2 as Position, GetNthPart( [TestString], 2, "_") as NthPart
FROM tblTest WHERE GetNthPart( [TestString], 2, "_") IS NOT NULL
UNION
SELECT 3 as Position, GetNthPart( [TestString], 3, "_") as NthPart
FROM tblTest WHERE GetNthPart( [TestString], 3, "_") IS NOT NULL
) as uq
GROUP BY uq.NthPart

This post has been edited by dale.fye: Jul 16 2019, 05:57 AM

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
gemmathehusky
post Jul 16 2019, 06:44 AM
Post#11


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


how are you trying to use mid


mid is either
a) a function to retrieve a portion of a string
result = mid(…..)

or
b) a statement to replace part of a string
mid (…) = "new string"


it's not standalone when used in vba.
simply mid(…) on its own is an error

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
cheekybuddha
post Jul 16 2019, 07:04 AM
Post#12


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Good spot, Dave (if indeed the OP was trying to use Mid() without assigning one way or the other). thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
strive4peace
post Jul 16 2019, 02:13 PM
Post#13


strive4peace
Posts: 20,447
Joined: 10-January 04



hi (what is your name?)

it seems that the field you want to separate contains more than one piece of information. To normalize the database better, each distinct piece should be stored in its own field. Is there any reason why this cannot be done? Normalizing helps performance and gives greater flexibility. We can guide you once we learn more.

What do the parts of this information represent?

Tables in Access databases often start out by importing data from somewhere else ... and usually, it needs to be parsed! Whenever you need something like GetNthPart, a good question to ask yourself is if the data is normalized. Would it be good to create fields (and maybe more tables), and use update (and append) queries to populate information? This would eliminate the need to keep on separating it.

~~~

David, thank you, that means so much. Dale, thanks for adding on

~~~

--------------------
have an awesome day,
crystal
Go to the top of the page
 
msfarrar
post Jul 16 2019, 02:21 PM
Post#14



Posts: 97
Joined: 11-July 12



I have put limits on my query to remove any blanks or nulls, and that did not resolve the problem. My goal is to have mid return a string

I ran a test on my testing string, and the middle section was output, so my math adds up. test string: "test_testing_test2" result: "testing"

I then converted those strings to my text field, and I am getting the same error.

I ran some diagnostics on my data, one row had # instead of anything else, so I deleted that row of data, and there were no nulls or blank cells.

After checking my data, I still get the same error.
Go to the top of the page
 
cheekybuddha
post Jul 16 2019, 02:28 PM
Post#15


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Please show the full SQL statement that you are using

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 06:38 AM