remove character during query

G

Guest

I am attempting to strip a character out of a part number during the query
process. The character I want to remove is always the last character in the
string, however, the string varies from 9-14 characters long. Is there an
easy way to remove the last character for all items at the same time?
 
T

Tom Ellison

Dear Doug:

Use the LEN() function to find out the length, subtract one from that,
and take the LEFT() portion of the string using the len-1 value:

LEFT(YourColumn, LEN(YourColumn) - 1)

That should do it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
G

Guest

Thanks. However, my length varies from 9 to 14 characters. If possible I
would like to get rid of the last character without having to sort by the
individual lengths. Is there a "right" function that I could use to skip the
last character and retrieve all the others?

Thanks for your help.
 
T

Tom Ellison

Dear Doug:

Take a moment and try what I gave you. The LEN() function measures
the length of the string dynamically, for each row. The LEFT function
then uses this lenght and gives just what you said you wanted.

There can be problems with this if there are any rows in which the
value has fewer than 2 characters, or is null. You might have to
accomodate that. Otherwise, if I understood your question, this is a
good answer.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
G

Guest

This is just what i was looking for. I misunderstood the LEN() function the
first time.

Thanks again.
 
D

Darrell Childress

I need to do something similar, but my situation is slightly different.
I have a text field called "DropSize" which contains entries like:

40x48
33X128
27x49

I need to extract whatever is before the "x" and what's after the "x"
and put into 2 separate NUMBER fields called "DropLength" and
"DropWidth". They need to be number fields so that I can then calculate
the area. Can this be accomplished?
Thanks,
Darrell
 
T

Tom Ellison

Dear Darrell:

Instead of LEN(), use INSTR() to locate the "X" in the string,
subtract 1, and use that length.

If there are any rows without an X or any rows where X is the first
character, they you will need additional code to handle those cases.
If there are more than one X in the string it will find the first one.
Again, if this is not what you want, you'll need to define exactly
what you wish and code for it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
D

Darrell Childress

Tom:
The InStr function worked great, although it took me a while to figure
out exactly how that function works (of course trial and error is one of
life's greatest teachers!). Don't know what I would do without this
newsgroup.
Many thanks,
Darrell
 
T

Tom Ellison

Dear Darrell:

It was not my intention to torture you. But having to look it up and
figure out how to apply it was probably an excellent exercise for you.
That was exactly my intent. I'm glad it worked.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top