help!

Les

Registered User.
Local time
Today, 23:42
Joined
Nov 20, 2002
Messages
45
Ok, I have a field that combined three different names separated by a comma (Test1 X. Test1, Test2 W. Test2, Test3 Q. Test3). Not all have three names though, some has only one and some two. I need to separate or break down this field into three.

so far, i have the following in three different text boxes:

'first
=Left([billtolname],InStr(1,[billtolname],",")-1)

'second
=Trim(Mid([billtolname],InStr(1,[billtolname],",")+1,IIf(InStr(InStr(1,[billtolname],",")+1,[billtolname],",")=0,0,InStr(InStr(1,[billtolname],",")+1,[billtolname],",")-InStr(1,[billtolname],",")-1)))

'third
=IIf(InStr(InStr([billtolname],",")+1,[billtolname],",")<>0,Right([billtolname],Len([billtolname])-InStr(InStr([billtolname],",")+1,[billtolname],",")),Right([billtolname],Len([billtolname])-InStr([billtolname],",")))

It's fine if there are three names, but if the field only has one name, i get an #error on the first box.

Can anyone tell me how to build a better trap for this mouse?


Thank You
 
Use the null to zero function (Nz) to convert the null fields to something you can handle, like a blank.

Later comment:
I don't think this posting really helped, did it? Sorry!
 
Last edited:
How about appending an extra comma and a space to your original data, then you will always have a trailing comma on a single name record and trimming should remove the space so that your second field remains empty.

Is this more helpful than the rubbish I posted first?
 
Les,


' ***********************************************
Dim Temp As String
Dim ptr As Integer
Dim Results(10) As String
Dim intLoop As Integer

Temp = [billtolname]

intLoop = 1
ptr = InStr(1, Temp, ",")
While ptr > 0
Results(1) = Left(Temp, ptr-1)
Temp = Mid(Temp, ptr+1, Len(Temp) - ptr - 1)
intLoop = IntLoop + 1
ptr = InStr(1, Temp, ",")
Wend
' ***********************************************

Or

' ***********************************************
Dim Temp As String
Dim ptr As Integer

Temp = [billtolname]

ptr = InStr(1, Temp, ",")
If ptr > 0 Then
Field1 = Left(Temp, ptr-1)
Temp = Mid(Temp, ptr+1, Len(Temp) - ptr - 1)
End If

ptr = InStr(1, Temp, ",")
If ptr > 0 Then
Field2 = Left(Temp, ptr-1)
Temp = Mid(Temp, ptr+1, Len(Temp) - ptr - 1)
Else
Field2 = "N/A"
End If

ptr = InStr(1, Temp, ",")
If ptr > 0 Then
Field3 = Left(Temp, ptr-1)
Else
Field3 = "N/A"
End If
' ***********************************************

hth,
Wayne
 
Thank you soo much Wayne.
I had to modify it to:


ptr = InStr(1, Temp, ",")-1
If ptr > 0 Then
Field1 = Left(Temp, ptr)
Temp = Mid(Temp, ptr+3, Len(Temp) - ptr)
else
field1 = temp
if ptr > 0 then Temp = Mid(Temp, ptr+3, Len(Temp) - ptr) else temp = "N/A"
if ptr > 0 then more=1 else more = 0
end if
End If
end if

ptr = instr(1,Temp,",")-1
if more = 1 and ptr > 0 then
Field2=Left(Temp,ptr) else field2 = temp
if more = 1 and ptr > 0 then
Temp = Mid(Temp, ptr+3, Len(Temp)- ptr) else Temp = "N/A"
if ptr > 0 then more=1 else more = 0
end if
end if
end if

ptr = instr(1,Temp,",")-1
if more = 1 and ptr > 0 then
Field3=Left(Temp,ptr) else field3 = temp
if more = 1 and ptr > 0 then
Temp = Mid(Temp, ptr+3, Len(Temp)- ptr) else Temp = "N/A"
if ptr > 0 then more=1 else more = 0
end if
end if
end if

=),
Les
 

Users who are viewing this thread

Back
Top Bottom