Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

Run Time Error 94

I have a parsetext which encounter a run time error 94 when I run the query make table.  

Public Function fnParseText(ParseWhat as Variant, Element as integer, Optional Delimiter as string = ",") as Variant

   Dim myArray() as string

   myArray = Split(ParseWhat, Delimiter)

   if Element < 0 OR Element > ubound(myArray) + 1 then

      fnParseText = NULL

  Else

      fnParseText = myArray(Element - 1)

   End If

End Function


How do I fix this to have the "" returned if it is null?

Hope I didn't cause more confusion.

Always appreciative.
0
ammiewinds
Asked:
ammiewinds
  • 7
  • 4
1 Solution
 
ammiewindsAuthor Commented:
Just for a little more info, here is the original question:

I have a field that is designed as follows: 
        ??/#######/????????/########/???

I would like to parse the field based on the "/".  The field is not alway consistant, so I would like to have the complete string show in the first parse, if the "/" is not available.

I have been tring the Left$ function:     
     Left$([Field Name],InStr(2,[Field Name],"/")-1)

This was able to bring in the first part.  Yet it gives me an error of "Func!" if the "/" is not in the string and I have not been successful in tring to retrieve the second portion.
0
 
Rey Obrero (Capricorn1)Commented:
try this revision

Public Function fnParseText(ParseWhat as Variant, Element as integer, Optional Delimiter as string = ",") as String

   Dim myArray() as string

If Instr(ParseWhat,Delimiter)=0 then
   fnParseText=ParseWhat
   exit function
end if

   myArray = Split(ParseWhat, Delimiter)

   if Element < 0 OR Element > ubound(myArray) + 1 then

      fnParseText = ""

  Else

      fnParseText = myArray(Element - 1)

   End If

End Function
0
 
ammiewindsAuthor Commented:
Hello carpricorn1

This returns the first part of the delinited cell in every movement, when the delimiter is not present in the string.

Some of my strings do not contain the delimiter, and I wan them to  show as

        Delimiter Example
           Cell 1                      Cell 2                  Cell 3
           data                       data                     data    

        No Delimiter Example
           Cell 1                      Cell 2                  Cell 3
           data                        no data              no data


When I parse them out I want the strings that do not have a delimiter to enter in the first cell, but not in the second, third...

Any thoughts?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Rey Obrero (Capricorn1)Commented:
how is the process done ? give more info./details
0
 
ammiewindsAuthor Commented:
Within my Make Table query I have the function set as:

CID 1: fnParseText([Raw Data Granite]![Circuit Name],1"/")
CID 2: fnParseText([Raw Data Granite]![Circuit Name],2,"/")...
0
 
Rey Obrero (Capricorn1)Commented:
ok.. use this



Public Function fnParseText(ParseWhat As Variant, Element As Integer, Optional Delimiter As String = ",") As String

   Dim myArray() As String
   myArray = Split(ParseWhat, Delimiter)

   If Element < 0 Or Element > UBound(myArray) + 1 Then
     
     'use this
      fnParseText = "No Data"
     'or
 
'      fnParseText = ""

  Else

      fnParseText = myArray(Element - 1)

   End If

End Function
0
 
ammiewindsAuthor Commented:
When I am in my query and use the View, it works.  Yet, when I try to run the Make Table Query I still get the run time error.
0
 
ammiewindsAuthor Commented:
It points me to this part of the script for debugging:

myArray = Split(ParseWhat, Delimiter)
0
 
ammiewindsAuthor Commented:
When I hover over "ParseWhat"  is shows: "=NULL"

This is the only place i have found NULL in the script.
0
 
Rey Obrero (Capricorn1)Commented:
oh, so you have a record with no value in field "Circuit Name"

try this revision

Public Function fnParseText(ParseWhat As Variant, Element As Integer, Optional Delimiter As String = ",") As String

if ParseWhat & ""="" then fnParseText="" : exit function

   Dim myArray() As String
   myArray = Split(ParseWhat, Delimiter)

   If Element < 0 Or Element > UBound(myArray) + 1 Then
     
     'use this
      fnParseText = "No Data"
     'or
 
'      fnParseText = ""

  Else

      fnParseText = myArray(Element - 1)

   End If

End Function
0
 
ammiewindsAuthor Commented:
Brilliant, Cap!  Thanks so much for your help.   Have a fabulous day!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now