Originally Posted by SteveH
Yes, I can see a pattern - it's repeated apart from those fields which have some kind of numeric attribute such as telephone number or date.
That is *NOT* the reason we use Replace( text, "'","''" ).
We do that to
(a) Prevent SQL injection. I'm not going to discuss that here. You can google for it. Find dozens if not hundreds of references.
(b) Allow for apostrophes in the text fields.
If you did *NOT* do that, then when you tried to do an INSERT such as:
INSERT INTO table (field) VALUES('I don't like cheese.')
You would get an error. Because the database would see that query as
INSERT INTO table (field) VALUES('I don' t like cheese.')
And it would have no idea at all what to do with t like cheese.'
The rule in Access and SQL Server for EMBEDDING apostrophes in database fields is the same as is the rule in VBScript for embedding quote marks: Use two in a row to signify one embedded mark.
So you *WANT* the query to look like
INSERT INTO table (field) VALUES('I don''t like cheese.')
Because then the text IN THE DATABASE will indeed be
I don't like cheese.
You can read more here:
(WOW! I can't believe I wrote that THIRTEEN YEARS ago next week! Double wow.)
By the by, as old as those ASPFAQs are, they are still almost all applicable. You could do worse than to read through all of them.