trim ( )LastName and First name ( , )

[Paul]

Hi All,
I have the following data in a field:

Arenaza, Gilbert
Brown, Kathy

I want to trim the characters after the ",".

Result:

Arenaza
Brown

What function do I use?

 

[RobFMS]

Paul

Is is a very simplified function as an example.

Public Function x()

Dim s As String
Dim intPosition As Integer

s = "Brown, Kathy"
intPosition = InStr(1, s, ",")
Debug.Print Left(s, Len(s) - intPosition - 1)

End Function

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Paul]

How do I put that in my query?

-----Original Message-----
Paul

Is is a very simplified function as an example.

Public Function x()

Dim s As String
Dim intPosition As Integer

s = "Brown, Kathy"
intPosition = InStr(1, s, ",")
Debug.Print Left(s, Len(s) - intPosition - 1)

End Function

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



.

 

[RobFMS]

First, make the function PUBLIC and a meaningful name, such as FixName().

In the case of a query, do the following:

Select Field1, Field2, Field3, ... FixName( FieldX ), ...
From <table>
Where <clause>

Whatever value is in the field (in this case FieldX), will be passed to the
function FixName. The function will expect to have a return value.

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[John Vinson]

Hi All,
I have the following data in a field:

Arenaza, Gilbert
Brown, Kathy

I want to trim the characters after the ",".

Result:

Arenaza
Brown

What function do I use?

Try:

Left([fieldname], InStr([fieldname], ",") - 1)

InStr finds the position of the comma; Left returns the substring up
to that point.

 

[Dale Fye]

If there is a chance that a field won't have a comma, modify Johns
statement to look like:

Left([fieldname], IIF(InStr([fieldname], ",") = 0, LEN([fieldname]),
INSTR([fieldname], ",") - 1))

Of course, there is also the possibility that the comma will be in the
1st position. If that is the case, even the code above will return an
error.



--
HTH

Dale Fye

Hi All,
I have the following data in a field:

Arenaza, Gilbert
Brown, Kathy

I want to trim the characters after the ",".

Result:

Arenaza
Brown

What function do I use?

Try:

Left([fieldname], InStr([fieldname], ",") - 1)

InStr finds the position of the comma; Left returns the substring up
to that point.