Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
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
 
Old December 4th, 2003, 07:37 PM
Authorized User
 
Join Date: Sep 2003
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
 
Old December 4th, 2003, 08:29 PM
Friend of Wrox
 
Join Date: Jun 2003
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.
 
Old December 4th, 2003, 08:45 PM
Authorized User
 
Join Date: Sep 2003
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
 
Old December 5th, 2003, 09:25 AM
Friend of Wrox
 
Join Date: Jun 2003
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.
 
Old December 30th, 2003, 05:09 PM
Registered User
 
Join Date: Dec 2003
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





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





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