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
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.