Solved

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

Posted on 2014-02-05
7
463 Views
Last Modified: 2014-02-05
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?
0
Comment
Question by:fitaliano
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:fitaliano
Comment Utility
In other words, I want to truncate the filed as soon as the number starts
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Are you asking for just a query?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:fitaliano
Comment Utility
it works but I get a #Func! error for fields without numberic values
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
IIf(instr(yourfield, "2")  = 0, yourfield, Left(yourfield, instr(yourfield, "2") -2))
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
 

Author Closing Comment

by:fitaliano
Comment Utility
Thank you
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now