By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

Help with custom use of Split()

P: 46
Hello all Experts,

I've inherited a .mdb with hundreds of thousands of records the problem is that there is no structure to the tables.
The issue I'm dealing with now is the following:
The table includes 5 columns, ID, Month1, Amount1, Month2, Amount2.
The problem is that each one of the Month an Amount fields have multiple records separated by the + sign.

Note: Not all the ID's have the same number of records in each column (in fact they very rarely do).

The question is how can I split each record to a separate field?

I've tried using this query
Expand|Select|Wrap|Line Numbers
  1. SELECT MyTable.ID, MyTable.Amount1, Testsplit([Amount1],0) AS Part1, Testsplit([Amount1],1) AS Part2, Testsplit([Amount1],2) AS Part3, Testsplit([Amount1],3) AS Part4, Testsplit([Amount1],4) AS Part5, Testsplit([Amount1],5) AS Part6, Testsplit([Amount1],6) AS Part7, Testsplit([Amount1],7) AS Part8, Testsplit([Amount1],8) AS Part9, Testsplit([Amount1],9) AS Part10
  2. FROM MyTable
The Testsplit function is:
Expand|Select|Wrap|Line Numbers
  1. Public Function TestSplit(strInput As String, intNr As Integer) As String
  2.  
  3.     Dim myArray() As String
  4.  
  5.     myArray = Split(strInput, "+")
  6.     If intNr <= UBound(myArray) Then
  7.         TestSplit = myArray(intNr)
  8.     Else
  9.         TestSplit = ""
  10.     End If
  11.  
  12. End Function
  13.  
Now this usually works well, but if any records are empty (no amount for the ID) that whole line is full of #error instead of not showing anything (or "")

Your assistance is most appreciated.
Feb 7 '12 #1

✓ answered by NeoPa

Wow. You know how to impress. This is a much better question (from Custom Split Help) and a pleasure to work with :-) Normally we don't allow the same question to be asked twice, but in view of the difference between the two, and that you were following instructions to the best of your understanding, instead of merging them and ending up with a confused thread, I'll close that one and leave it just for reference and allow this one to stand.

On to the problem, which is that strInput is defined as a String. This is fine if MyTable.Amount1 is a string, but for empty records it's likely to be Null (Blanks in fields are generally stored as Null rather than empty strings, though this can be controlled in the field setup.). As such, it's better to declare the parameter as a Variant rather than a String, and then handle the possibility in line #4 as follows :
Expand|Select|Wrap|Line Numbers
  1. Public Function TestSplit(varInput As Variant, intNr As Integer) As String
  2.     Dim myArray() As String
  3.  
  4.     myArray = Split(Nz(varInput, ""), "+")
  5.     TestSplit = ""
  6.     If intNr <= UBound(myArray) Then TestSplit = myArray(intNr)
  7. End Function
Line #5 is optional as TestSplit, as a string value, is automatically initialised to an empty string ("") anyway.

Let us know if this resolves your problem :-)

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Wow. You know how to impress. This is a much better question (from Custom Split Help) and a pleasure to work with :-) Normally we don't allow the same question to be asked twice, but in view of the difference between the two, and that you were following instructions to the best of your understanding, instead of merging them and ending up with a confused thread, I'll close that one and leave it just for reference and allow this one to stand.

On to the problem, which is that strInput is defined as a String. This is fine if MyTable.Amount1 is a string, but for empty records it's likely to be Null (Blanks in fields are generally stored as Null rather than empty strings, though this can be controlled in the field setup.). As such, it's better to declare the parameter as a Variant rather than a String, and then handle the possibility in line #4 as follows :
Expand|Select|Wrap|Line Numbers
  1. Public Function TestSplit(varInput As Variant, intNr As Integer) As String
  2.     Dim myArray() As String
  3.  
  4.     myArray = Split(Nz(varInput, ""), "+")
  5.     TestSplit = ""
  6.     If intNr <= UBound(myArray) Then TestSplit = myArray(intNr)
  7. End Function
Line #5 is optional as TestSplit, as a string value, is automatically initialised to an empty string ("") anyway.

Let us know if this resolves your problem :-)
Feb 7 '12 #2

P: 46
It most definitely solves the problem. Thank you!!! :-)
Just to verify: what data type in stored in the array, and will that have any effect on writing the data to a table?
Feb 7 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
My pleasure :-)

The array is defined on line #2 and is of type String. As such "Yes".

Depending on the setup of your table, it may (most do) require empty values to be stored as Nulls. This can be handled by not setting the value at all if the string value is empty (""), or alternatively, using an array of Variants and maintaining the Null value as passed.
Feb 7 '12 #4

Post your reply

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