Isolating Characters Left of Comma

[Anthony]

I have a database where both Last Name and First Name are
combined in a single field. ie, Jones, Laura. For
comparison purposes I need to compare only the last name
within this field to another table.field that is only the
last name. Length of last name and position of comma vary
from record to record so MID and Left commands don't seem
to work.

Is there a command that will allow me to isolate only the
characters that exist left of the comma?

 

[Ki Yi [MS]]

Hello,

Following kb article should help you on this issue:

ACC2000: Sample Expressions to Extract Portion of Text String WGID:147
ID: 209045.KB.EN-US
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q209045

Regards,

Ki Yi
Microsoft Support

This posting is provided AS IS with no warranties, and confers no rights.

 

[RobFMS]

As long as the data is in a consistent format (i.e. LastName, FirstName),
you can use the Split() function (in Access 2000 & higher). Use the comma as
the delimiter. The help file will give you the syntax.

HTH

--
Rob

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

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

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