Text Manipulation

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

What expression would I use to return all the text in a
field after the 3rd character.

Example:

"abcdefg"

Returns:

"defg"

Thanks
Chad
 
A combination of two statements:

Len(<string> ) for determining the number of chars in a <string>.

Mid(<string>, <start>, <count> ) for returning a string from <string>
starting at char <start> and retrieveing <count> number of chars.

Example being:

Dim strTEMP as String

strTEMP = "abcdefg"

strTEMP = Mid(strTEMP, 4, Len(strTEMP) - 3)

Think this does it for ya, but I can never remember if the first char is 1
or 0 without looking at docs. Play around.

Casey
 
Im sorry I don't think I was clear enough. I wanted to do
this via a query in SQL. Is there a way I can do this?
-----Original Message-----
A combination of two statements:

Len(<string> ) for determining the number of chars in a
Click to expand...
 
Dear Chad:

What Casey told you works in a query in a typical MDB. You can use
the built in function in a query as a calculated column.

It can be shortened, however:

strTEMP = Mid(strTEMP, 4)

The third parameter can be omitted if what you want is the rest of the
string.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
What if the data is not a set number of characters?

Here is exactly what I am dealing with...

There is a field in a table that contains the names of
employees. Unfortunately it is listed as First initial,
Middle initial, Last Name so my name being Chad Anthony
Lucy would be CALUCY. Also, if the person doesnt have a
middle name that space is left blank (but is still there)

I am trying to return just people's last name.

Any suggestions?
 
Dear Chad:

Your example makes it look like you just want to leave off the first
two characters instead of the first 3. So change to:

Mid(YourFieldName, 3)

Create a calculated column in the query based on this. Try it. I
think it will do what you want.

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