?
Solved

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

Posted on 2005-03-08
12
Medium Priority
?
301 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
[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
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

771 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