• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 62
  • Last Modified:

trying to look at textbox value but not working on variable check

Access vba

I'm trying to get a value from a textbox(email address) and assign to a variable..

Then before inserting variable into sql statement replace the ' with a ""

BIIL.O'CONNER@RRR.COM

But this statement will not even match on the variable ?

Me.CALLER_EMAIL.SetFocus
eStr = Me.CALLER_EMAIL.Text


If eStr = "BILL.O'CONNOR@RRR.COM" Then
   eStr = "BILL.OCONNOR@RRR.COM"
End If

IF BILL.O'CONNOR@RRR.COM  is the variable eStr it still will not hit on the If....Then  statement ?

Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 5
  • 5
  • 3
  • +2
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
Please try this -

If eStr = "BILL.O''CONNOR@RRR.COM" Then
   eStr = "BILL.OCONNOR@RRR.COM"
End If
0
 
FordraidersAuthor Commented:
That means i would have to change the email address before hand ?
0
 
Pawan KumarDatabase ExpertCommented:
example ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Martin LissOlder than dirtCommented:
How about
eStr = Replace(Me.CALLER_EMAIL.Text,"'","")

You then don't need the If/End If.
0
 
Pawan KumarDatabase ExpertCommented:
Pls try this -
eStr  = replace ( eStr  , "'", "")
0
 
FordraidersAuthor Commented:
going from the data entry  BILL.O'CONNOR@RRR.COM    to   BILL.O''CONNOR@RRR.COM

I would have to insert the extra    '

fordraiders
0
 
Pawan KumarDatabase ExpertCommented:
Pls try this if you need extra ' ( single quote )
eStr  = replace ( eStr  , "'", "''")
0
 
FordraidersAuthor Commented:
martin, Yes that would do...But WHY is the code not picking up the text ?

dp
0
 
Martin LissOlder than dirtCommented:
Probably because the ' is being tread as the comment symbol, so everything after it is ignored.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Fordraiders,

As you can see this has been an issue in Access for a while.
This is also an issue with apostrophes: ex:
    Jeffrey's

The:
    '
...Can be used as both a Character, and a text Qualifier/Escape character.

There is no real easy way to get around this except for the Replace() suggestions the experts have proposed.

The other thing is that you could do, is to modify the design of the table so that the email address does not have to be a direct concatenation of First Name and last name:

For example:
TblEmployees
EmpID (PK), EmpFN, EmpLN, EmpEmail

examples...:
45,FRED, SMITH,FREDDY.SMITH@abc.org
(nicknames)

52, JOYCE, CARMAN, JOYCE.ADAMS@abc.org
(Married/Maiden Name issues)

and finally:
76, BILL, O'CONNOR, BILL.OCONNOR@abc.org

This way you can lookup the email address, and use that as your variable:
ex.:
    Dlookup("EmpEmail","tblEmployees","EmpID=" & me.txtEmpID)

;-)

JeffCoachman
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Another possibility if you only want to match is to hash it..
0
 
FordraidersAuthor Commented:
This seemed to solve my problem !


DIM YourName string

YourName = "Daniel O'Neal"

  If InStr(YourName, "'") Then
      SELECT * FROM tblStudents WHERE [name]  Like """ Your Name """ ;
   else
      SELECT * FROM tblStudents WHERE [name] Like '" Your Name "' ;      
  end if
0
 
Pawan KumarDatabase ExpertCommented:
Great, cheers..
0
 
FordraidersAuthor Commented:
Thanks to all,...
0
 
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now