Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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


Reply With Quote
  #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>
Reply With Quote
  #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

Reply With Quote
  #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>
Reply With Quote
  #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



Reply With Quote
  #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>
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:57 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.