• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Access Form VB. Error MSG when using an apostrophe in text value via SQL

I am using a variable to capture the text that the user enters.  This is used in the SQL per the appropriate field and works without issue unless;  an apostrophe is used in the text.  Example:

dim variable1 as string
 
variable1 = "  hello there Sidney" - this works fine
variable1 = " hello it's Sidney" - this gives me the error.

variable1 = '" & variable1 &"' in the SQL


Thank you.
0
ellenjbr
Asked:
ellenjbr
  • 4
  • 4
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Instead of:

variable1 = '" & variable1 &"' in the SQL

  Do this:

variable1 = chr$(34) & variable1 & chr$(34)

JimD
0
 
ChumadCommented:
You can also do a replace on the string to turn in the single quote into a double single quote if you want to preserve it in the database as a single quote:

variable1 = " hello it's Sidney"

'replaces all single quotes with a double-single quote
variable1 = variable1.Replace("'", "''")
0
 
ellenjbrAuthor Commented:
jimd .... it doesn't work.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ellenjbrAuthor Commented:
chumad:  What you posted, is that a practical example or if not, please post one.  Thank you.
0
 
ChumadCommented:
Actually, it would be practical if that were VB.NET and not Access. :)  

Let me dig a bit and look up the replace function in access. I'll post back here when I find it.
0
 
ChumadCommented:
ok, here's a page that show's how to use replace in access:  http://www.techonthenet.com/access/functions/string/replace.php

One thing I'd do first though just to test this out - change your string manually to 'hello it''s sidney' (note that those are two single quotes, not a double quote). If it does indeed save for you, go ahead with the replace to find all single quotes and convert them to two single quotes.
0
 
ellenjbrAuthor Commented:
Thank you, I will look at it and get back with you.
0
 
ChumadCommented:
Did you have any luck?
0
 
ellenjbrAuthor Commented:
hi ... not yet, this is a hectic week ... I will attempt this or next week and get back with you ... thank you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now