How to delete the LAST four characters in a field?

[Guest]

I have a field of SIC Codes with an additional four numbers/characters added
to the end of the number. Is there a way I can strip or delete the last four
characters? Since all of the numbers do not have the same amount of
characters I can't use the MID function. Some data is 6 or 7 or 8 characters
long. So I need to work from the right and delete the last four numbers only.
Am I making sense?
Perhaps I could determine the number of characters first and then perform a
MID statement based on the number of characters in the field? Like 'if field
is x then do aMID, if field is y then do bMID' ? Is that possible?
I'm lost. Any help is much appreciated.

 

[chriske911]

I have a field of SIC Codes with an additional four numbers/characters added

to the end of the number. Is there a way I can strip or delete the last four
characters? Since all of the numbers do not have the same amount of
characters I can't use the MID function. Some data is 6 or 7 or 8 characters
long. So I need to work from the right and delete the last four numbers only.
Am I making sense?
Perhaps I could determine the number of characters first and then perform a
MID statement based on the number of characters in the field? Like 'if field
is x then do aMID, if field is y then do bMID' ? Is that possible?
I'm lost. Any help is much appreciated.

=LEFT([field];LEN([field])-4)

grtz

 

[TC]

Typo: needs a comma, not a semicolon.

HTH,
TC [MVP Access]

 

[John Vinson]

Typo: needs a comma, not a semicolon.

a regional difference - some European-language installations of Access
use the semicolon.


John W. Vinson[MVP]

 

[TC]

Are you sure that is so for parameter lists? (Not disagreeing, just
asking.) I thought that VBA sourcecode was never regionalized. Eg. you
can always say "open", even in a French installation - you do not need
to say "oeuvert" (or whatever it is in French). If the comma /had to/
be a semicolon, in some installations, that would make the VBA source
non portable, no?

TC [MVP Access]

 

[John Vinson]

Are you sure that is so for parameter lists? (Not disagreeing, just
asking.) I thought that VBA sourcecode was never regionalized. Eg. you
can always say "open", even in a French installation - you do not need
to say "oeuvert" (or whatever it is in French). If the comma /had to/
be a semicolon, in some installations, that would make the VBA source
non portable, no?

TC [MVP Access]

I'm really not sure, Tim! I've certainly seen the semicolon in VBA
code from European posters though.

John W. Vinson[MVP]

 

[TC]

Oh dear, my secret name has clearly been deduced from the announcement
email !!

Of course, I'll have my full name & some biographical details in the
website. But I think that I will probably keep posting as TC. It's got
a sorta' nice feel about it, by now

Interesting about the semicolons in VBA code. I thought that code was
never localized. Clearly I haven't read enough foreign code. (OK: I
have read /no/ foreign code!)

Cheers,
TC [MVP Access]

 

[TC]

Oh dear, my secret name has clearly been deduced from the announcement
email !!

Of course, I'll have my full name & some biographical details in the
website. But I think that I will probably keep posting as TC. It's got
a sorta' nice feel about it, by now

Interesting about the semicolons in VBA code. I thought that code was
never localized. Clearly I haven't read enough foreign code. (OK: I
have read /no/ foreign code!)

Cheers,
TC [MVP Access]

 

[TC]

Ooh, my secret name is no longer a secret!

I'll have a website up soon, with my full name & some biographial
details. I actually posted this yesterday (in reply to you, in this
thread), but I currently post through google groups, and google seems
to have lost most of my last day-or-so's posts :-(

As for the semicolon issue, I didn't know the source could ever be
localized. Thanks for that info.

Cheers,
TC [MVP Access]

 

[John Vinson]

Oh dear, my secret name has clearly been deduced from the announcement
email !!

I'll keep your secret safe, TC! <g>

John W. Vinson[MVP]