eliminating numbers in a text field with formula in a query (MS Access)

I have a field with values like the followings:

Banc of America Funding  2006-A Trust
Banc of America Funding  2004-2 Trust

What formula can I use in an access query to just retain

Banc of America Funding?
fitalianoAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
IIf(instr(yourfield, "2")  = 0, yourfield, Left(yourfield, instr(yourfield, "2") -2))
0
 
fitalianoAuthor Commented:
In other words, I want to truncate the filed as soon as the number starts
0
 
PatHartmanCommented:
If all you have to worry about is year's that start with 2, then

Left(yourfield, instr(yourfield, "2") -2)

The expression uses instr() to find the "2" and then subtracts 2 to get the length of the string to extract.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
hnasrCommented:
Are you asking for just a query?
0
 
fitalianoAuthor Commented:
it works but I get a #Func! error for fields without numberic values
0
 
hnasrCommented:
Here is a query for first digit 0, 1, 2, or 3. Modify query if higher digits are involved.
For table a (adesc)

SELECT adesc,
left(replace(replace(replace( replace(adesc,"0","9"),  "1","9"),"2","9"),"3","9"),
instr(replace(replace(replace( replace(adesc,"0","9"),  "1","9"),"2","9"),"3","9"), "9")-1)
FROM a

Open in new window


You may need to check for null values.
0
 
fitalianoAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.