Solved

Extract part of information from a text field on Access 2003 form

Posted on 2013-01-28
17
521 Views
Last Modified: 2013-02-08
I have a field on a form (text1)that displays the complete smtp email address for a cell phone.I am using  a command button on click event to try to extract the actual phone number up to the “at” sign and place it in text2 . Is this doable?

Text1= 3345552654@vtext.com
Text2=3345552654
0
Comment
Question by:mickeyshelley1
  • 6
  • 4
  • 3
  • +2
17 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38827859
me.text2 = left(me.text1, instr(me.text1, "@")-1)
0
 
LVL 75
ID: 38827860
Val("3345552654@vtext.com")

 returns 3345552654

mx
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 38827873
Use a function such as
Left(me.Text1,Instr(1,Me.text1,"@")-1) in a query or VBA to return the number

Kelvin
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38827883
you could set the control source of text2 to:

=Left([Text1], instr([Text1] & "@", "@")-1)

If there is no @ symbol in the [Text1] it will return the entire string
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38827894
Joe,

I keep forgetting about that option.
0
 
LVL 75
ID: 38827910
Val() ... too simple !
0
 
LVL 75
ID: 38827929
Private Sub btnSomeButtonName_Click()
    Dim x
    x =  Val(Me.YourTextBoxName)
 End Sub
0
 

Author Comment

by:mickeyshelley1
ID: 38828226
Thanks this works great...
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 38828250
:-)

Let's see.  Two functions vs one ... hummm.

valCapture1.gif
0
 

Author Comment

by:mickeyshelley1
ID: 38828294
“Every man is my superior in that I may learn from him.”


¿ Thomas Carlyle
0
 

Author Comment

by:mickeyshelley1
ID: 38828359
I apologize for not evaluating post by DatabaseMX on 2013-01-28 at 18:37:02ID: 38827929 after trying this approach it did work equally as well with less clutter. It's simplicity caused me me to overlook it fearing I didn't completely understand it. I will not make this mistake again.
0
 
LVL 75
ID: 38828368
:-(

" It's simplicity caused me me to overlook it fearing I didn't completely understand it"
Really ?  In general, Less is always More ...
0
 

Author Comment

by:mickeyshelley1
ID: 38828392
lol i understand, i requested that the moderator review my awarding of points, you have provided me with sound advice over the years, i acted in haste...
0
 
LVL 75
ID: 38828413
no worries ...
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38828623
Here lies Lester Moore
Shot dead by a .44
No les
No more
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38828994
Redistribute away!  I was wondering why you selected mine, after MX posted his elegant solution.  For some reason, I always forget about that option.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 - Labels prompt to print 4 30
Trying to open Visio 2013 from a ms access 2016 database 9 30
Modal form 11 29
is Microsoft Access going to Die? 9 42
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

813 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

13 Experts available now in Live!

Get 1:1 Help Now