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 | 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 December 4th, 2003, 07:37 PM
Authorized User
 
Join Date: Sep 2003
Location: , , Canada.
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default Storing single and double quotes in text

Greeting all,

Does anyone know how to store the ' or " symbol in a text field? I know you can use the double '' to represent a single one but I'm not sure if there is a simple way to do this.
To this point I have been somewhat of a weenie and told the staff to use the double single quotes (''), but Access has a bizarre behavior of auto correcting that when you least expect it and changing it back, sometimes while typing, other times perhaps during the storing and loading of the text value. It would be particularly useful on longer notes and comment text fields, much like the one I am typing into at this very moment. This DB is also using SQL server as an ODBC back end if that makes any difference.

Thanks in advance for any advice you may supply.
Best Regards
-Roni

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com
__________________
Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com
Reply With Quote
  #2 (permalink)  
Old December 4th, 2003, 08:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oxford, , United Kingdom.
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Roni

If you're using unbound forms for your data entry it's easy - just use the replace function to change ' to '' before you write the data back to the database.

For a bound form, you could probably do something similar in the form's BeforeUpdate event.

In either case the auto correct won't be a problem as it only works when the user is typing.


Brian Skelton
Braxis Computer Services Ltd.
Reply With Quote
  #3 (permalink)  
Old December 4th, 2003, 08:45 PM
Authorized User
 
Join Date: Sep 2003
Location: , , Canada.
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Brian,
I can't believe it is that easy. I feel both exhilerated and stupid both at the same time.
Can it be used for more than one item? This is how I am using it so far:

UDATE [Tab] SET
x = " & replace(x.value,"'","''")
WHERE ...

the on load I am using the same reverse scenario
x.value = replace(rs![x],"''","'")Do you just run through it twice to replace both ' and & like this:
x.value = replace(replace(rs![x],"''","'"),"&&","&")

or can you send it some fabulous and exciting arguments? Sorry, there is no examples in my MS help.

Regarsd,
-Roni

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com
Reply With Quote
  #4 (permalink)  
Old December 5th, 2003, 09:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oxford, , United Kingdom.
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Roni

Yes, you can nest the replace functions - although whether you need to depends on what your datasource considers to be a special character (e.g. a ' character to denote the end of a string) and what translation the ODBC driver does on the data passed through it.

When presenting the data to the user, you don't need to use the replace function. When '' is passed to the SQL database the first ' is treated as an escape character when combined with a second '. You're basically saying yes, I really did want to store a ' character in the text field. If you look at the data in the table you will see that only one ' character has actually been stored.


Brian Skelton
Braxis Computer Services Ltd.
Reply With Quote
  #5 (permalink)  
Old December 30th, 2003, 05:09 PM
Registered User
 
Join Date: Dec 2003
Location: Batesville, Indiana, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to ggriffit
Default

Ok, in this case, what about putting the data with a single or double quote back into a text field? If it is stored in SQL with a single or double quote, when you put the statement value=" and text is there with a double quote, it stops the value statement when the first " is met. Any way to tell it to continue?

Thanks!
Grant


Quote:
quote:Originally posted by Braxis
 Roni

Yes, you can nest the replace functions - although whether you need to depends on what your datasource considers to be a special character (e.g. a ' character to denote the end of a string) and what translation the ODBC driver does on the data passed through it.

When presenting the data to the user, you don't need to use the replace function. When '' is passed to the SQL database the first ' is treated as an escape character when combined with a second '. You're basically saying yes, I really did want to store a ' character in the text field. If you look at the data in the table you will see that only one ' character has actually been stored.


Brian Skelton
Braxis Computer Services Ltd.
Grant Griffith
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
Please help! i hate...double and single quotes... keita Classic ASP Basics 3 March 28th, 2007 11:12 AM
Please explain single and double quotes rsgracey BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 4 June 8th, 2006 07:20 PM
displaying single quotes and double quotes ren_123 Classic ASP Databases 2 February 22nd, 2006 02:17 PM
Double Quotes and Single Quotes Problem phungleon Classic ASP Basics 7 May 27th, 2004 01:44 PM
Getting single and double quotes into DB hoffmann Classic ASP Databases 9 December 4th, 2003 12:24 AM



All times are GMT -4. The time now is 07:39 AM.


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