Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# VBA double quote escaping question

Posted on 2016-10-06
Medium Priority
86 Views
How do you compare two strings when one is
3"
and the other is "3"""
which is the same 3 inches but with the double quote within the string escaped.
I want to do a straight "is string equal"
0
Question by:Silas2
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +1

LVL 49

Expert Comment

ID: 41832354
You could do

If Val(first string) = Val(second string) Then
0

LVL 49

Assisted Solution

Martin Liss earned 500 total points
ID: 41832364
Or in a formula, something like

=IF(SUBSTITUTE(A1,CHAR(34),"")=SUBSTITUTE(B1,CHAR(34),""),"Equal","Not equal")
0

Author Comment

ID: 41832366
I was thinking along those lines, and stripping all the quotes out, but that's a bit crude and wouldn't catch quite a few possibilities, (although I have to think quite hard to come up with any but in large(ish) volumes there might be some)
0

LVL 75

Expert Comment

ID: 41832382
If Replace(<SomeVar1>,Chr(34),"") = Replace (<SomeVar2>, Chr(34),"") then
'whatever
Else
'whateverelse
Endif
0

LVL 49

Expert Comment

ID: 41832406
I was thinking along those lines, and stripping all the quotes out, but that's a bit crude and wouldn't catch quite a few possibilities, (although I have to think quite hard to come up with any but in large(ish) volumes there might be some)
If you think of a possibility, let me know.
0

LVL 39

Assisted Solution

PatHartman earned 500 total points
ID: 41832434
That's just one of the many problems that mushing multiple attributes into a single field causes.  Use one field for just the number and the second for the unit of measure.  EVERY field should be atomic.  That's what first normal form specifies.
0

LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 41832448
If only it were a perfect world :-)
0

LVL 39

Expert Comment

ID: 41832516
I can't tell you how many of these situations I have fixed over the years.  It's almost always better to simply resolve the data problem by splitting the mushed field once.  Then everything else just works better.  Of course if you have no control over the table schema you're SOL.
0

## Featured Post

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll