Number Text Conversion Error

[Tamrak]

Good afternoon,

I have a text field that I downloaded from another table. It contains information such as "3.2.1.4.5". This refers to the topics on our standard operating procedures. In this example, 3 is the main topic. 2 is the sub-topic of 3 and 1 is a sub-topic of 2 and so on...

I was able to get the information to my calculated field with the main topic number. This is what I want.

However, the problem started when I have a null or blank value in that downloaded field. When I ran the query by calling the function below, it created a neverending loop with "#ERROR" (only for the blank or null value.) The codes are the following:


Function EliminateDot(FieldString As Variant) As String
Dim SplitIt
SplitIt = Split(FieldString, ".")
If UBound(SplitIt) >= 0 Then
EliminateDot = SplitIt(0)
Else
EliminateDot = SplitIt(UBound(SplitIt))
End If
End Function

I recommend adding an If statement into this code. I do appreciate your help. Thanks.

 

[PHV]

Function EliminateDot(FieldString As Variant) As String
Dim SplitIt
If Trim(FieldString & "") <> "" Then
SplitIt = Split(FieldString, ".")
EliminateDot = SplitIt(UBound(SplitIt))
Else
EliminateDot = FieldString
End If
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tamrak]

Good afternoon PH,

Thank you for your reply.

The original function returned the value that I was looking for. Your function returned the second value. For example, the text was: "3.2.1.4.5", it returned 2, instead of 3.

I still having problem with the blank or null value. The error, #Error, is still there.

Any suggestions? Thanks.

 

[PHV]

Function EliminateDot(FieldString As Variant) As String
If Trim(FieldString & "") <> "" Then
EliminateDot = Split(FieldString, ".")(0)
End If
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tamrak]

Good afternoon PH

Thank you. This is working fine.

I do appreciate your prompt response. An additional star for you.

Thanks again.