ACCESS FEED: Re: [MS_AccessPros] replace(me.control,""","")

Rabu, 27 Februari 2013

Re: [MS_AccessPros] replace(me.control,""","")

Liz

Trying to handle double quotes always makes me dizzy. I use the Chr() function. Chr(34) is a double-quote. So if you want to replace a double with an empty string do this:
Replace(Me.Text0, Chr(34), "")

Or if you want to replace a double-quote with a single one:
Replace(Me.Text0, Chr(34), Chr(39))

Of course, if Text0 is null or might be then you need to test for that.
If Len(Me.Text0 & vbNullString)<>0 Then
Replace(Me.Text0, Chr(34), Chr(39))
End If

But then you have to change your string to:
strSQL = "Insert into tmptable textfield values (" _
& Chr(34) & strTest & Chr(34) & ");"

Capeesh?

Bill

--- In MS_Access_Professionals@yahoogroups.com, Liz Ravenwood wrote:
>
> John, I'm getting a syntax error on my test.
>
> This bit of code:
> Me.Text0 = Replace(Me.Text0, """", """""")
> Dim strsql As String
> strsql = "Insert into tmptable textfield values ('" & Me.Text0 & "');"
> CurrentDb.Execute (strsql)
>
> From this text box value:
> this is a "test"
>
> Gives this strsql result:
> Insert into tmptable textfield values ('this is a ""test""');
>
> Respectfully,
> Liz Ravenwood
> Programmer/Analyst
> Super First Class Products
> B/E Aerospace
> O: 1.520.239.4808
> www.beaerospace.com
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Tuesday, February 26, 2013 3:15 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] replace(me.control,""","")
>
> Liz-
>
>
>
> If you suspect "bad" quotes in an incoming string, do this:
>
>
>
> Me.txtStuff = Replace(Me.txtStuff, """", """""")
>
>
>
> That will embed double-double quotes in the string that will work OK if you're using double quotes to delimit your string.
>
>
>
> John Viescas, Author
>
> Microsoft Access 2010 Inside Out
>
> Microsoft Access 2007 Inside Out
>
> Microsoft Access 2003 Inside Out
>
> Building Microsoft Access Applications
>
> SQL Queries for Mere Mortals
>
> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
> Sent: Tuesday, February 26, 2013 11:03 PM
> To: MS_Access_Professionals@yahoogroups.com'
> Subject: [MS_AccessPros] replace(me.control,""","")
>
>
>
>
>
> Pros,
>
> I cannot believe that I've been fortunate enough that my users have been trained well enough not to use the double quotes in some of my stuff that relies on an sql statement behind the scenes, but I need to get rid of double quotes or at least replace them with an acceptable char.
>
> How do I do this?
>
> Respectfully,
> Liz Ravenwood
> Programmer/Analyst
> Super First Class Products
> B/E Aerospace
> O: 1.520.239.4808
> www.beaerospace.comhttp://www.beaerospace.com>
>
> This email (and all attachments) is for the sole use of the intended
> recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
> This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
>