 |
| Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
July 12th, 2007, 12:57 PM
|
|
Authorized User
|
|
Join Date: Jul 2007
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Message> in query expression <expression>. (Error
I've created a Report Generator in Access VBA that uses a Filter Command.
When I use data that includes a single quot I get an error 3075.
Otherwise data with no single quot works fine.
How can I get over this problem?
Here is the code:
' Build criteria string from lstAuthor listbox
For Each varItem In Me.lstAuthor.ItemsSelected
strAuthor = strAuthor & ",'" & Me.lstAuthor.ItemData(varItem) _
& "'"
Next varItem
|
July 13th, 2007, 03:26 AM
|
|
Friend of Wrox
|
|
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
|
|
ybg1,
The Single Quote basically tells the SQL Parser that the String has finished,
and the characters following that will cause it to fall over.
To avoid this, when working with Strings, replace with TWO single quotes.
e.g (for your code)
Code:
' Build criteria string from lstAuthor listbox
Dim selAuthor as String
selAuthor = Replace$(Me.lstAuthor.ItemData(varItem),"'", "''")
For Each varItem In Me.lstAuthor.ItemsSelected
strAuthor = strAuthor & ",'" & selAuthor & "'"
Next varItem
I hope this helps,
Rob
<center> "Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>
|
July 14th, 2007, 06:05 AM
|
|
Authorized User
|
|
Join Date: May 2006
Location: , , India.
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
You may avoide this using following method too.
This is just an example I used in my db. you may please replace the variables of your choice.
Set rst1 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where Productname= '" & Replace(CboSearchItem, Chr(39), Chr(39) & Chr(39)) & "'")
Hope this will help,
With kind regards
Ashfaque
|
July 14th, 2007, 06:39 AM
|
|
Friend of Wrox
|
|
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Ashfaque,
Thats the same thing!? (Well we could be pedantic and argue the toss over execution speeds of Replace/Replace$, and 3x function calls to Chr - but lets not!! =) )
One point I did fail to mention is to make sure you use Nz when referencing Control values, or you will get a Invalid Use of Null error thrown if the value has not been set.
Regards,
Rob
<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>
|
July 15th, 2007, 02:16 AM
|
|
Authorized User
|
|
Join Date: Jul 2007
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi!
Thank you Rob and Ashfaque for your suggestions.
It was very helpful.
Regards,
Ybg
|
July 15th, 2007, 05:42 AM
|
|
Friend of Wrox
|
|
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Ybg,
No problem, happy to be of help!
Rob
<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>
|
|
Similar Threads
