update (remove last 4 characters)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

_Bigred - 23 Mar 2006 08:22 GMT

I setup my update query trying to remove the last 4 of a FileName

I tried

LEFT([Powertabs],4) but this left only the first 4 letters of the file name.

I tried RIGHT([Powertabs]),4 but this gave me .ptb

Ultimately the file names would be as such

Green Day - American Idiot.ptb
Bon Jovi - Livin On A Prayer.ptb

I would like to be able to run a update query and remove the .ptb from the
file names.

TIA,
_Bigred

Reply to this Message

Allen Browne - 23 Mar 2006 08:29 GMT

Try:
Left([Powertabs], Len([Powertabs]) - 4)

If the extensions could more more than 3 characters (e.g. mypage.html or
mypic.jpeg), you might want to use InstrRev() to locate the position of the
last dot in the file name.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I setup my update query trying to remove the last 4 of a FileName
>
[quoted text clipped - 15 lines]
> TIA,
> _Bigred

Reply to this Message

_Bigred - 23 Mar 2006 09:12 GMT

sorry to sound foolish ... do I put the InstrREV where Len is? and then
remove the -4?

Thanks Allen,
_Bigred

> Try:
> Left([Powertabs], Len([Powertabs]) - 4)
[quoted text clipped - 22 lines]
>> TIA,
>> _Bigred

Reply to this Message

Allen Browne - 23 Mar 2006 12:17 GMT

Assuming all entries have a dot before the last character:

Left([Powertabs], InstrRev([Powertabs], ".") - 1)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> sorry to sound foolish ... do I put the InstrREV where Len is? and then
> remove the -4?
[quoted text clipped - 25 lines]
>>> I would like to be able to run a update query and remove the .ptb from
>>> the file names.

Reply to this Message

_Bigred - 23 Mar 2006 23:21 GMT

Thanks Allen that worked like a charm!

Thanks much,
_Bigred

> Assuming all entries have a dot before the last character:
>
[quoted text clipped - 29 lines]
>>>> I would like to be able to run a update query and remove the .ptb from
>>>> the file names.

Reply to this Message