How do I Excel formula =LEFT(A2,LEN(A2)-3) in an Access Query

[Lou]

I need to delete the last 12 characters (phone number) from a field that
contains names and phone numbers.

 

[Jeff Boyce]

Lou

Are you asking how to do this in MS Access? If so, take a look in Access
HELP re: Mid() function and Len() function.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

I need to delete the last 12 characters (phone number) from a field that
contains names and phone numbers.

Surprisingly, it's

Left([fieldname], Len([fieldname]) - 12)

just like the Excel expression. It does assume that the last 12 characters are
*always and reliably* a 12-character phone number - e.g.

"Joe Jones (no phone)"
"Brian Molatu - 255 48-124"

would lose data.

 

[John Spencer]

The expression you need would be
Left([SomeField],Len(SomeField)-12)

How you use the expression would depend on what you need to do.

To show the Name without the phone number you would use a calculated column in
a query.

Field: NameOnly: Left([SomeField],Len(SomeField)-12)

To permanently change the contents of the field, you would need an update
query where you update the field to the expression. Of course, if you do that
you will lose the phone number forever.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County