Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Double Quote (") problem in SQL expressions in MS Access form filter

Posted on 2005-03-08
12
Medium Priority
?
306 Views
Last Modified: 2006-11-17
Dear experts,

I need to use field values, selected by user from a combo box of all values in a field,
to filter the records showed in a form, like that:
Forms(frm.Name)![subfrm].Form.Filter = strSQL
were strSQL is somethink like:
(([ACCOUNT_NAME] LIKE "SomeCompany""))
I have a problem with double quotes inside the string.
How can I submit in SQL expressions a string with double quotes inside it?

Thanks and tell me if the points are enough.
0
Comment
Question by:prismabi
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 18

Expert Comment

by:Billystyx
ID: 13487643
I think a / before the " sorts that out

Billystyx
0
 
LVL 18

Accepted Solution

by:
Billystyx earned 225 total points
ID: 13487654
alternatively, &"""" &"SomeCompany &""""
billystyx
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 150 total points
ID: 13487717
Alternatively, when you build the SQL string:

strSQL="[ACCOUNT_NAME] Like """ & Replace(strCompany,"""","""""") & """"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Expert Comment

by:cstahlberg
ID: 13488058
also a:

 & chr(132)

would add in a quote to  string...

0
 

Author Comment

by:prismabi
ID: 13488084
Actually the best answer for me is from shanesuebsahakarn
thanks to you. I'm looking for more answers from you.
0
 

Author Comment

by:prismabi
ID: 13488127
I suggest declaring:
Const QUOTE = """"

so the syntax is more friendly:
strSQL="[ACCOUNT_NAME] Like """ & Replace(strCompany,QUOTE ,"""""") & QUOTE

Thanks again to all experts.
0
 
LVL 18

Expert Comment

by:Billystyx
ID: 13490138
Is there any difference in points between accepted answer and assisted here? If so, I think you can swap them round ...

Billystyx

0
 

Author Comment

by:prismabi
ID: 13491100
That's the first time I grant points here, so I'm not sure if they were splited as i wanted:
75 to Billystyx
50 to shanesuebsahakarn

>you wrote: you can swap them round
Sorry but i don't understand what you mean.


0
 

Author Comment

by:prismabi
ID: 13491248
shanesuebsahakarn,
thanks again for your very good answer.
It worth more points than I gave you!
The next time i'll add 250 points to your answer.

0
 
LVL 18

Expert Comment

by:Billystyx
ID: 13491273
sorry, I just thought you meant that you intended to give shane the higher cut - so I was suggesting you could get it changed that way if that was what you meant, but I'm sure Shane doesn't mind anyway for the difference of 25 points:)

Billystyx

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13491277
It's not like I need them :)
0
 
LVL 18

Expert Comment

by:Billystyx
ID: 13491402
That's very true!
Billystyx
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

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.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

578 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