I am trying to extract the first part a lastname field which would not 

include the comma and degree of a physician.  The example is as follows:



SMITH, M.D.



How do I do this in ACCESS?  Any help would be appreciated ASAP.  Thanks!!

Try this code:

	Left("SMITH, M.D.",instr("SMITH, M.D.",",") -1)



This will return the first part of the field, upto the first comma

found.  This is assuming that the field will contain a comma.  



If you are going to do this in a query replace the "SMITH, M.D." bit

with your field name. Eg:



Left(MyField,instr(MyField,",") -1)



Try reading the manual on InStr(), Right(), Left() and Mid().  They are

your best friends when in comes to quick and dirty string manipulation.



Padgett



-----Original Message-----

From: msavoy@h... [mailto:msavoy@h...] 

Sent: Wednesday, 12 September 2001 2:44 PM

To: Access

Subject: [access] String manipulation question



I am trying to extract the first part a lastname field which would not 

include the comma and degree of a physician.  The example is as follows:



SMITH, M.D.



How do I do this in ACCESS?  Any help would be appreciated ASAP.

Thanks!!

Try this



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



This example looks for the "," and will take everything left of it. 

I can tell you in VB. I'm sure it will translate into Access. I would

normally do this in several lines of code, but you may need to do this in

one line. So, I'll start w/ each step, then combine.



I would declare two variables for this: intVariable to hold the location of

text within my field's string and strLastName to capture the last name.



Find out the location of the comma:

intVariable = instr(Field,",")



Now we know where the comma is. Next step, subtract 1 to get us to the last

character that we care about:

intVariable = intVariable - 1



Now, we just take the leftmost portion of the field:

strLastName = left(Field,intVariable)



In a single line, without needing variables, it would be:

left(field,instr(field,",") - 1)



Hope this helps,

Paul





-----Original Message-----

From: msavoy@h... [mailto:msavoy@h...]

Sent: Wednesday, September 12, 2001 2:44 PM

To: Access

Subject: [access] String manipulation question





I am trying to extract the first part a lastname field which would not 

include the comma and degree of a physician.  The example is as follows:



SMITH, M.D.



How do I do this in ACCESS?  Any help would be appreciated ASAP.  Thanks!!

Thanks to everyone for their time and help.  It is appreciated.







> I can tell you in VB. I'm sure it will translate into Access. I would

> normally do this in several lines of code, but you may need to do this in

> one line. So, I'll start w/ each step, then combine.

> 

> I would declare two variables for this: intVariable to hold the location 

of

> text within my field's string and strLastName to capture the last name.

> 

> Find out the location of the comma:

> intVariable = instr(Field,",")

> 

> Now we know where the comma is. Next step, subtract 1 to get us to the 

last

> character that we care about:

> intVariable = intVariable - 1

> 

> Now, we just take the leftmost portion of the field:

> strLastName = left(Field,intVariable)

> 

> In a single line, without needing variables, it would be:

> left(field,instr(field,",") - 1)

> 

> Hope this helps,

> Paul

> 

> 

> -----Original Message-----

> From: msavoy@h... [mailto:msavoy@h...]

> Sent: Wednesday, September 12, 2001 2:44 PM

> To: Access

> Subject: [access] String manipulation question

> 

> 

> I am trying to extract the first part a lastname field which would not 

> include the comma and degree of a physician.  The example is as follows:

> 

> SMITH, M.D.

> 

> How do I do this in ACCESS?  Any help would be appreciated ASAP.  

Thanks!!