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

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.

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

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.

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


Grant Griffith