473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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 -

Expand|Select|Wrap|Line Numbers
  1. 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...

Expand|Select|Wrap|Line Numbers
  1. SplitJEDI_NPI("00A398390*330861255*RIVERSIDE REGIONAL PEDIATRIC M")
  2.  
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. Expr1: SplitJEDI_NPI([CAPRVSOURCE])
The [CAPRVSOURCE] is defined as a Text field in the table.

Can anyone help me out? Thank you in advance!


Expand|Select|Wrap|Line Numbers
  1. Public Function SplitJEDI_NPI(ByVal InputString As Variant)
  2.  
  3.     Dim strInput As String
  4.     'Declare an array that will hold data elements
  5.     Dim strArrayNPI() As String
  6.     Dim strResult As String
  7.  
  8.     strInput = InputString
  9.  
  10.     'Debug.Print strInput
  11.  
  12.     Erase strArrayNPI
  13.  
  14.     'Split input string and store as an array
  15.     strArrayNPI = Split(strInput, "*")
  16.  
  17.     'Trim the results
  18.     strResult = Trim(strArrayNPI(0))
  19.  
  20.  
  21.     Debug.Print strResult
  22.  
  23.     SplitJEDI_NPI = strResult
  24.  
  25. End Function
Oct 16 '14 #1
3 1256
iam_clint
1,208 Expert 1GB
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.
Oct 17 '14 #2
twinnyfo
3,653 Expert Mod 2GB
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.
Oct 17 '14 #3
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT diamond_JEDIHSM0_ISG.CACLAIM, diamond_JEDIHSM0_ISG.CAPRVSOURCE, SplitJEDI_NPI([CAPRVSOURCE]) AS ProviderNPI
  2. FROM diamond_JEDIHSM0_ISG;
  3.  
[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_JEDIHSM0_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!
Oct 17 '14 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Mark | last post by:
Not sure this is the right place for this questions, but here goes: I get an error message when deleting an table from a Access database. The code is as follows and the error message is after...
1
by: intl04 | last post by:
I am getting strange print-related error messages when trying to create (not print!) reports. For example, when I click 'new' to create a report then choose 'design view', I get an error message...
2
by: Alpha | last post by:
Hi, I just added a new form with Crystl report viewer but one of my other form suddently starts giving me an error message when I rebuild the code. The error is complainig about the class name. ...
1
by: יוני גולדברג | last post by:
Hi, While trying to create new directory i recieve the following error message: "System.IO.DirectoryNotFoundException: Could not find a part of the path "\\premfs16\sites". The path exists, even...
3
by: Benny Raymond | last post by:
I get the following error message when trying to use the Excel Interop on my wife's machine however I don't get it on my own - we have the same version of Office installed - what could be the...
1
by: Gordon | last post by:
Hi; What can I not fill my dataset once I have connected to my Excel ss. When I get to the fill ds part of my code I get the following exception " "The Microsoft Jet database Engine could...
0
by: daniel_xi | last post by:
Hi all, I am getting this error message when I try to open a project in Visual Studio .NET 2003. Now, this is happening only for one of my projects, the rest are fine. This particular project is...
1
by: =?Utf-8?B?RG9t?= | last post by:
I have been trying to back up my hard drive using Vista's built-in back up. It has worked before but now i keep getting this message.... The backup did not complete successfully. An error...
10
by: surferj | last post by:
I am beginning to receive an error message when trying to execute the .Send command when using the CDO.Configuration and CDO.Message in a W2k3 server environment on WXP machines and IE6. Below is a...
1
by: Yogendra kul | last post by:
Hi: I got an error message when I am trying a select Query to import a table from Ms Access Database (.MDB File) in SQL Server 2005.i am using SQL Server 2005. The query I am testing is as below:...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.