Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old July 12th, 2007, 12:57 PM
Authorized User
Points: 110, Level: 2
Points: 110, Level: 2 Points: 110, Level: 2 Points: 110, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2007
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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


  #2 (permalink)  
Old 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
Default

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>
  #3 (permalink)  
Old July 14th, 2007, 06:05 AM
Authorized User
 
Join Date: May 2006
Location: , , India.
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

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

  #4 (permalink)  
Old 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
Default

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>
  #5 (permalink)  
Old July 15th, 2007, 02:16 AM
Authorized User
Points: 110, Level: 2
Points: 110, Level: 2 Points: 110, Level: 2 Points: 110, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2007
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi!

Thank you Rob and Ashfaque for your suggestions.

It was very helpful.


Regards,

Ybg



  #6 (permalink)  
Old 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
Default

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
Thread Thread Starter Forum Replies Last Post
<bean:message key="error.access.rights"/> sparun1607 Struts 1 September 23rd, 2011 03:08 AM
Regular Expression to remove <table> </table> tags mathalete CSS Cascading Style Sheets 2 January 23rd, 2006 01:59 PM
<style> tags in a <body> vs. <div> bcat BOOK: Beginning CSS: Cascading Style Sheets for Web Design ISBN: 978-0-7645-7642-3 1 March 27th, 2005 08:50 AM
<marquee><b>About CHAT App. in PHP4</b></marquee> Ramkrishna PHP How-To 1 September 11th, 2004 07:01 AM
<STRONG> vs <B> and <EM> vs <I> anshul HTML Code Clinic 12 September 1st, 2004 05:22 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.