TRIM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How would I code if I only wanted John to show up.

Say the table fields could be:

John, Doe
John
John Doe
BLANK

How would I code this? I just want the first string and
nothing else

Thanks, Kim
 
For the examples you show, you could use:
Left$(fld_name,4) = "John"

However, if you also wanted to find "John" in a field that
contains "Doe, John", you could use:
InStr$(1, fld_name, "John") > 0

Good luck,

Chuck
 
What I mean is I only want the first string before a
comma, a blank, or Null.

If it was:

John, Doe
Pauline Smith
Blank

I want the first string

John
Pauline
 
How would I code if I only wanted John to show up.

Say the table fields could be:

John, Doe
John
John Doe
BLANK

How would I code this? I just want the first string and
nothing else

Thanks, Kim
Click to expand...
Kim,
The Trim() function (if that is what you are referring to) removes
extra spaces before and after the string, not text within a string.

You have a problem with the layout of your names which is going to
cause a problem. They are not all in the same format.

You could write a user defined function.
Place it in a module.

Public Function ParseName(FieldIn As String) As String
Dim strName As String
Dim intX As Integer
If InStr(FieldIn, " ") = 0 Then
ParseName = FieldIn
Exit Function
End If
intX = InStr(FieldIn, ",")
If intX > 0 Then
strName = Left([FieldIn], InStr([FieldIn], ",") - 1)
Else
intX = InStr(FieldIn, " ")
If intX > 0 Then
strName = Left([FieldIn], InStr([FieldIn], " ") - 1)
End If
End If
ParseName = strName
End Function
=====
Call it from a query.
FirstName:ParseName([FullName])

Set the criteria for the [FullName] field to:
Is Not Null
 
Thank you
-----Original Message-----
How would I code if I only wanted John to show up.

Say the table fields could be:

John, Doe
John
John Doe
BLANK

How would I code this? I just want the first string and
nothing else

Thanks, Kim
Click to expand...
Kim,
The Trim() function (if that is what you are referring to) removes
extra spaces before and after the string, not text within a string.

You have a problem with the layout of your names which is going to
cause a problem. They are not all in the same format.

You could write a user defined function.
Place it in a module.

Public Function ParseName(FieldIn As String) As String
Dim strName As String
Dim intX As Integer
If InStr(FieldIn, " ") = 0 Then
ParseName = FieldIn
Exit Function
End If
intX = InStr(FieldIn, ",")
If intX > 0 Then
strName = Left([FieldIn], InStr([FieldIn], ",") - 1)
Else
intX = InStr(FieldIn, " ")
If intX > 0 Then
strName = Left([FieldIn], InStr([FieldIn], " ") - 1)
End If
End If
ParseName = strName
End Function
=====
Call it from a query.
FirstName:ParseName([FullName])

Set the criteria for the [FullName] field to:
Is Not Null
--
Fred
Please respond only to this newsgroup.
I do not reply to personal email
.
Click to expand...
 
Back
Top