KMK said:
I've got a field that could contain 1, or 01, or 01A, or 01B, or 1C and I'm
trying to get read of the A, B, and C. Output would be 1 or 01. Maybe trim
is not the way to do it?
....
You mean "get rid", right?
The most generalisable way of handling data like this is "Regular
Expressions" - a facility which allows you to express the general
structure of a data item and access its components. Astonishingly
powerful once you get the hang of it. I've never had occasion to use
them in Access (although I regularly use them in other tools) so I
looked for a suitable web reference for you. The best seems to be this one:
http://www.accessmvp.com/DJSteele/SmartAccess.html
(see the second item).
The time invested in learning regular expressions WILL be repaid.
However, Regular Expressions are for those used to (or prepared to
learn) some quite intricate coding.
Instead, you might play around with the functions LEFT, RIGHT, MID, LEN,
INSTR and ISNUMERIC. Alternatively, you could set up a SELECT CASE
block to match specific values. You'll see it's quite easy to lift
specific characters from a specific string - the tricky bit is to do the
right thing when presented with a variety of different combinations, and
you'll need a clear understanding of what might appear in that field
before you can write the appropriate code. Once you've worked out how
to extract the numeric part reliably from whatever data might appear in
your tables, you may want to wrap the code in a function in a VBA module
and call that in your query, so you can refer to
=JustNumeric([stringfield]). Be sure to include error handling code:
see:
http://allenbrowne.com/ser-23a.html
One thought - these couldn't be Hex numbers, could they?
Phil, London
