How can I strip the extensions from filenames in a query?

[David Anderson]

I have an Access 2003 table created by importing an Excel spreadsheet. One
field in this table contains the filenames of digital images in various
formats. These filenames are of varying lengths and have extensions such as
..jpg, .jpeg or .tif (other file extensions are also possible). For example,

A red car.jpg
A blue bus.tif
Mountain view.jpeg

How can I write an query expression to strip off the extension, and which
will work regardless of the length of the extension? Essentially, I want to
drop the dot and everything that follows so that I end up with,

A red car
A blue bus
Mountain view

David

 

[Rick Brandt]

I have an Access 2003 table created by importing an Excel
spreadsheet. One field in this table contains the filenames of
digital images in various formats. These filenames are of varying
lengths and have extensions such as .jpg, .jpeg or .tif (other file
extensions are also possible). For example,
A red car.jpg
A blue bus.tif
Mountain view.jpeg

How can I write an query expression to strip off the extension, and
which will work regardless of the length of the extension?
Essentially, I want to drop the dot and everything that follows so
that I end up with,
A red car
A blue bus
Mountain view

David

Left(FieldName, InStr(FieldName,".")-1)

 

[David Anderson]

Rick,
Thanks for that quick and simple solution. I was pretty certain it was a
straightforward requirement but I have limited awareness of all the string
manipulation functions, e.g. I had never heard of InStr() before. That's one
of the problems of being a self-taught programmer - there tend to be gaps in
one's knowledge,

Is there somewhere in the Access 2003 online help where all the string
functions are listed?

David

 

[Rick Brandt]

Rick,
Thanks for that quick and simple solution. I was pretty certain it
was a straightforward requirement but I have limited awareness of all
the string manipulation functions, e.g. I had never heard of InStr()
before. That's one of the problems of being a self-taught programmer
- there tend to be gaps in one's knowledge,

Is there somewhere in the Access 2003 online help where all the string
functions are listed?

Possibly. I honestly don't think I've ever opened the help file for Access
2003. The expression builder is a good way to see all functions organized by
category (at least it was in older versions).

 

[David Anderson]

You are quite correct. The Expression Builder does indeed provide a list of
all the built-in text functions. I never thought of looking there, probably
because there is no way (as far as I know) to view the Expression Builder
while creating an expression within a query.

Thanks again.

David