#Error message when trying to use custom user-defined Split Function in query

[anclark]

Dear Experts,

I am trying to use the Split() function to parse a long string of data separated by the * symbol in a field. Here is an example of what this string looks like -

00A398390*330861255*RIVERSIDE REGIONAL PEDIATRIC M

I think I have put together the function correctly (see code at bottom of post) because it works when I test it in the immediate window in vba.

Using the above example I get the result I want...

SplitJEDI_NPI("00A398390*330861255*RIVERSIDE REGIONAL PEDIATRIC M")

Returns this: 330861255

Beautiful and exactly what I want, but when I try my function in a query, I get the #Error message in the query field.

This is what it looks like in the query grid, which results in #Error:

Expr1: SplitJEDI_NPI([CAPRVSOURCE])

The [CAPRVSOURCE] is defined as a Text field in the table.

Can anyone help me out? Thank you in advance!

Public Function SplitJEDI_NPI(ByVal InputString As Variant)

    Dim strInput As String
    'Declare an array that will hold data elements
    Dim strArrayNPI() As String
    Dim strResult As String
    
    strInput = InputString
    
    'Debug.Print strInput
    
    Erase strArrayNPI
    
    'Split input string and store as an array
    strArrayNPI = Split(strInput, "*")
    
    'Trim the results
    strResult = Trim(strArrayNPI(0))
    
    
    Debug.Print strResult
    
    SplitJEDI_NPI = strResult

End Function

[iam_clint]

I'd like to see the error message however you could get index out of bounds in your function if the string does not contain *. You should check the array size before trying to pull a value by index.

I'm not super familiar with access but I also assume you can't use a custom vba function in the query itself.

[twinnyfo]

anclark,

I, too, would like to see the error you are receiving. However, what would be more helpful is to see your query. There may be a reason why the error is being produced, without it being a problem with your custom function.

BTW, @iam_clint, MS Access can use custom functions in queries, as long as they are public. I use them all the time and can be very usefull when one has very complex calculations that aren't suitable for SQL.

[anclark]

Hi Everyone,

Ok this morning I tried again and my custom function worked fine! I don't know what happened, I must have been experiencing something buggy in Access yesterday. Here is my query:

SELECT diamond_JEDIHSM0_ISG.CACLAIM, diamond_JEDIHSM0_ISG.CAPRVSOURCE, SplitJEDI_NPI([CAPRVSOURCE]) AS ProviderNPI
FROM diamond_JEDIHSM0_ISG;

[CAPRVSOURCE] is the string I am splitting. Yesterday when I viewed the query in datasheet view I was getting #Error in the field where I was trying to use my function. The table [diamond_JEDIHSM 0_ISG] is a linked SQL table though so maybe there was something going on with the network. Anyway, thought I would post just in case it might help someone else. Thank you for replying to me!