Search and replace the last 8 characters? **

[ExcessAccess]

I want to delete the last 8 characters of a certain field [*????????], for
every record.

Can a search and replace do this? How do I find the last 8 characters of a
field?

 

[Mike Labosh]

I want to delete the last 8 characters of a certain field [*????????], for

every record.

Can a search and replace do this? How do I find the last 8 characters of
a
field?

You could use an update query like this:

UPDATE YourTable
SET Mid$([YourColumn, Len([YourColumn]) - 7, 8) = YourNewValue

 

[ExcessAccess]

Thanks much!
What does the $ sign do?

Jarrod

I want to delete the last 8 characters of a certain field [*????????], for
every record.

Can a search and replace do this? How do I find the last 8 characters of
a
field?

You could use an update query like this:

UPDATE YourTable
SET Mid$([YourColumn, Len([YourColumn]) - 7, 8) = YourNewValue

--
Peace & happy computing,

Mike Labosh, MCSD
"I have no choice but to believe in free will."

 

[Randy]

I want to delete the last 8 characters of a certain field [*????????], for
every record.

Can a search and replace do this? How do I find the last 8 characters of
a
field?

ExcessAccess, could you please clarify what you are asking for.

On the subject you ask to search and replace the last 8 characters. On the
other hand, your message reads as you just want to drop the last 8
characters. Which one is what you are looking for?

-Randy

 

[ExcessAccess]

Thank you, it is the latter. I want to delete the last 8 characters.

The reason for that bit of confusion is because I initially thought I might
search for "the last 8 characters" and replace with "nothing."

Jarrod

I want to delete the last 8 characters of a certain field [*????????], for
every record.

Can a search and replace do this? How do I find the last 8 characters of
a
field?

ExcessAccess, could you please clarify what you are asking for.

On the subject you ask to search and replace the last 8 characters. On the
other hand, your message reads as you just want to drop the last 8
characters. Which one is what you are looking for?

-Randy

 

[Randy]

Thank you, it is the latter. I want to delete the last 8 characters.

The reason for that bit of confusion is because I initially thought I
might
search for "the last 8 characters" and replace with "nothing."

Jarrod

Jarrod, the Search and Replace action from the Edit system menu does not
perform this kind of field modification. However you can use an Update query
on the field in question, setting its new value to include all the
characters of the field (starting from the left side) which length is equal
to the original length minus 8. That will drop the last 8 characters. That
is:

NewValue = Left(OldValue, Len(OldValue)-8)

So, for "1234567890"
You get "12"

You should also watch out for cases where the field length doesn't even have
8 character to subtract from. Not only the logic fails, but also get an
run-time error. So you should use this approach on the Update query by
having:

UPDATE Table
SET Field = Left([Field], Len([Field])-8 )
WHERE ( Len(Nz([Field], "")) > 8 )

Also note that I have used the Nz function, which takes care of any Null
value found, by replacing it with an empty string ("").

Does that help you?

-Randy

I want to delete the last 8 characters of a certain field [*????????],
for
every record.

Can a search and replace do this? How do I find the last 8 characters
of
a
field?

ExcessAccess, could you please clarify what you are asking for.

On the subject you ask to search and replace the last 8 characters. On
the
other hand, your message reads as you just want to drop the last 8
characters. Which one is what you are looking for?

-Randy

 

[Mike Labosh]

What does the $ sign do?

The VBA string-munching functions, Left(), Right() and Mid(), as well as
others, come in two flavors:

func() ' Returns a Variant. Variants are EVIL. They HATE you. They
HATE your users, too.
func$() ' Returns a String. Strings are nice things to have.