String Manipulation 
Author Message
 String Manipulation

I have a field in a table with names in such a format:

lastname~firstname.

I need to parse out the ~ (tilda) as well as switch first
name and last name around. Being new to Access and VBA I'd
thought I'd point here for help in how to achieve this.

Thanks in advance
Shannon



Sun, 30 May 2004 00:16:26 GMT  
 String Manipulation
Use the Instr() function to find the numerical position of the tilda then
use the mid() function to split up the string into 2 parts.


Quote:
> I have a field in a table with names in such a format:

> lastname~firstname.

> I need to parse out the ~ (tilda) as well as switch first
> name and last name around. Being new to Access and VBA I'd
> thought I'd point here for help in how to achieve this.

> Thanks in advance
> Shannon



Sun, 30 May 2004 00:50:46 GMT  
 String Manipulation
U can do this entirely in a query. No need for VBA.
In the build query window make field like:

Name: (Right([YourTableFieldName],InStr([YourTableFieldName],"~")-1)) & " "
& (Left([YourTableFieldName],InStr([YourTableFieldName],"~")-1))

Now this works if field is not null. Fields that are null have "#Error"
listed in the returned record set. Nz does not work to put something else
there. Does any one know why?

Paul


Quote:
> I have a field in a table with names in such a format:

> lastname~firstname.

> I need to parse out the ~ (tilda) as well as switch first
> name and last name around. Being new to Access and VBA I'd
> thought I'd point here for help in how to achieve this.

> Thanks in advance
> Shannon



Sun, 30 May 2004 06:05:28 GMT  
 String Manipulation
Try:

 Name: IIf(IsNull([YourTableFieldName]), "",
(Right([YourTableFieldName],InStr([YourTableFieldName],"~")-1)) & " "
 & (Left([YourTableFieldName],InStr([YourTableFieldName],"~")-1)))

You can't just wrap Nz around the expression you posted, as you get errors
in the various string functions. This means you don't actually have a Null
to pass to the Nz function.

Another approach is just to concatenate an empty string to your values, like
[YourTableFieldName] & "".

HTH

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


Quote:
> U can do this entirely in a query. No need for VBA.
> In the build query window make field like:

> Name: (Right([YourTableFieldName],InStr([YourTableFieldName],"~")-1)) & "
"
> & (Left([YourTableFieldName],InStr([YourTableFieldName],"~")-1))

> Now this works if field is not null. Fields that are null have "#Error"
> listed in the returned record set. Nz does not work to put something else
> there. Does any one know why?

> Paul



> > I have a field in a table with names in such a format:

> > lastname~firstname.

> > I need to parse out the ~ (tilda) as well as switch first
> > name and last name around. Being new to Access and VBA I'd
> > thought I'd point here for help in how to achieve this.

> > Thanks in advance
> > Shannon



Sun, 30 May 2004 06:26:25 GMT  
 String Manipulation
Mark, I've followed your advice and created the following
procedure. However, I'm getting this error when I run it,
any ideas what is wrong? "Run Time Error '5': Invalid
procedure call or arguement."

Basically, I've narrowed it down to the charPos value
returned from InStr is messing things up...if I take it
out and hard code it with a number its fine...thx in
advance...Shannon

Function strSwap(MyStr As Variant) As Variant
    Dim lName As String, fName As String, cName As String,
searchChar As String
    Dim charPos As Integer

    ' Exit if the passed value is null.
    If IsNull(MyStr) Then Exit Function

    ' Exit if the passed value is not a string.
   If VarType(MyStr) <> 8 Then Exit Function

    searchChar = "~"    ' search for "~"
    charPos = InStr(1, MyStr, searchChar)

    'fName = Mid(MyStr, charPos)
    lName = Mid(MyStr, 1, charPos - 1)
    'cName = fName + " " + lName

    strSwap = lName
End Function



Sun, 30 May 2004 09:13:58 GMT  
 String Manipulation
If the character you're searching for is not in the string, InStr() will
return zero, and then charPos - 1 will return a negative number, which would
be an invalid argument to the string parsing functions. Try testing charPos
for > 0.

--

Brendan Reynolds


Quote:
> Mark, I've followed your advice and created the following
> procedure. However, I'm getting this error when I run it,
> any ideas what is wrong? "Run Time Error '5': Invalid
> procedure call or arguement."

> Basically, I've narrowed it down to the charPos value
> returned from InStr is messing things up...if I take it
> out and hard code it with a number its fine...thx in
> advance...Shannon

> Function strSwap(MyStr As Variant) As Variant
>     Dim lName As String, fName As String, cName As String,
> searchChar As String
>     Dim charPos As Integer

>     ' Exit if the passed value is null.
>     If IsNull(MyStr) Then Exit Function

>     ' Exit if the passed value is not a string.
>    If VarType(MyStr) <> 8 Then Exit Function

>     searchChar = "~"    ' search for "~"
>     charPos = InStr(1, MyStr, searchChar)

>     'fName = Mid(MyStr, charPos)
>     lName = Mid(MyStr, 1, charPos - 1)
>     'cName = fName + " " + lName

>     strSwap = lName
> End Function



Sun, 30 May 2004 18:11:00 GMT  
 String Manipulation
Tks Douglas

this does the trick

Paul



Quote:
> Try:

>  Name: IIf(IsNull([YourTableFieldName]), "",
> (Right([YourTableFieldName],InStr([YourTableFieldName],"~")-1)) & " "
>  & (Left([YourTableFieldName],InStr([YourTableFieldName],"~")-1)))

> You can't just wrap Nz around the expression you posted, as you get errors
> in the various string functions. This means you don't actually have a Null
> to pass to the Nz function.

> Another approach is just to concatenate an empty string to your values,
like
> [YourTableFieldName] & "".

> HTH

> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele



> > U can do this entirely in a query. No need for VBA.
> > In the build query window make field like:

> > Name: (Right([YourTableFieldName],InStr([YourTableFieldName],"~")-1)) &
"
> "
> > & (Left([YourTableFieldName],InStr([YourTableFieldName],"~")-1))

> > Now this works if field is not null. Fields that are null have "#Error"
> > listed in the returned record set. Nz does not work to put something
else
> > there. Does any one know why?

> > Paul



> > > I have a field in a table with names in such a format:

> > > lastname~firstname.

> > > I need to parse out the ~ (tilda) as well as switch first
> > > name and last name around. Being new to Access and VBA I'd
> > > thought I'd point here for help in how to achieve this.

> > > Thanks in advance
> > > Shannon



Sun, 30 May 2004 22:43:06 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. string manipulation, string routines, etc.

2. String manipulation

3. VBA String Manipulation DLL Missing

4. String manipulation in VBA

5. String Manipulation

6. String manipulation with vbscript

7. String manipulation topic

8. String manipulation...

9. Text String Manipulation

10. Newbie question : string manipulation with VBscript.

11. String manipulation

12. String manipulation

 

 
Powered by phpBB® Forum Software