Add on Coding to skip the blank record 2

[Tamrak]

Good afternoon,

Can you assist me with this function?

I have several data in the table that have a format + + + + between each string. Sometimes, it looks like, Jeff+Smith+Barber+50+25 etc. Anyway, I can get the information that I am looking for.

The following is the code. It is working fine.

********************
Function TestString(FieldString As Variant) As String
Dim SplitEm
SplitEm = Split(FieldString, "+")
If UBound(SplitEm) >= 5 Then
TestString = SplitEm(5)
Else
TestString = SplitEm(UBound(SplitEm))
End If
End Function
*************************

Let's say the field name called, employeedata. I created a query, DataTest:Teststring([employeedata]).

However, the code generates an error when it hits the blank record. Can you assist in writing another if statement to skip the blank record? Otherwise, '94' error continues to populate.

Thanks a lot.

 

[HarleyQuinn]

You could check for NULL's or just return blank if it's NULL, I've gone for option 2, try something like:

Function TestString(FieldString As Variant) As String
Dim SplitEm
SplitEm = Split(Nz(FieldString,"+"), "+")
If UBound(SplitEm) >= 5 Then
   TestString = SplitEm(5)
Else
   TestString = SplitEm(UBound(SplitEm))
End If
End Function

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jrbarnett]

Function TestString(FieldString As Variant) As String
Dim SplitEm

if Len (FieldString & "") > 0 Then
  SplitEm = Split(FieldString, "+")
  If UBound(SplitEm) >= 5 Then
     TestString = SplitEm(5)
  Else
     TestString = SplitEm(UBound(SplitEm))
  End If
Else
  TestString = ""
End If
End Function

John

 

[Tamrak]

Thanks to all. It is working !