truncate end of record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to truncate the last few digits of a record, back to where the first
space occurs from the end of the record. For instance for:
AM 1 I need AM returned
AM 10 I need AM returned
Pen Spec 5 I need Pen Spec returned
Pen Spec 32 I need Pen Spec returned
How do I search for the first space in the record, starting from the end of
the record, and truncating every up to that point, including the space?
Thanks for your help.
 
Steve said:
I need to truncate the last few digits of a record, back to where the first
space occurs from the end of the record. For instance for:
AM 1 I need AM returned
AM 10 I need AM returned
Pen Spec 5 I need Pen Spec returned
Pen Spec 32 I need Pen Spec returned
How do I search for the first space in the record, starting from the end of
the record, and truncating every up to that point, including the space?
Thanks for your help.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you have Access 2002, or higher version, you can use the InstrRev()
function and the Left() function (debug window):

? Left("AM 1",InStrRev("AM 1"," ")-1)
AM

In a query - like this:

SELECT Left(column, InStrRev(column, " ")-1)
FROM table
WHERE <criteria>

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQl7VgoechKqOuFEgEQKyBQCeIRmvBXw4DPenQnjmuLOT2j4WDsYAoK1s
XgLFOWNOratLwqhs0y7iqIMX
=yhqs
-----END PGP SIGNATURE-----
 
Thanks alot. It works great. To delete the trailing blank space, I just
started the expression with the Trim function and it gets me exactly what I
need. Thanks again.
 
Back
Top