Chr(34) v/s double quotes

Thanks Guys

I prefer to use a function I wrote a long time ago, which involves less typing and ensures that quotes embedded in the string are doubled up as well.

Public Function Wrap(WrapWhat as Variant, _
                     Optional WrapWith as string = """") as Variant

    'you may or may not want to include this line.  I use it so that if I need 
    'to write a NULL value to a SQL Insert statement, it will accept a NULL 
    'value and return "NULL"
    if IsNULL(WrapWhat) then = "NULL"

    'This line replaces characters embedded in WrapWhat with two of them
    'It then wraps the entire value with the WrapWhat characters
    Wrap = WrapWith _
              & Replace(WrapWhat, WrapWith, WrapWith & WrapWith) & WrapWith

End Function

Select allOpen in new window

In your example above, you would use this like:

strSomeVar = "Some Text " & Wrap(strUserInput) & " Some more Text"

I originally excluded the WrapWith and just hard coded in the chr(34) values, but then, as I started using SQL Server and realized that I need to wrap text in single quotes, and that this would work for date values as well, I added in the WrapWith argument, so now I can wrap values in quotes (the default), single quotes: Wrap(somevalue, "'"), or even pound signs: Wrap(DateValue, "#").

Hi Dale,

Thanks for that very smart option. I would have given some point for that but unfortunately I have already closed the question

Cheers

SB9

I was just adding on.  I knew points had already been awarded, but wanted to impart that little trick that I use.  It makes reading the code so much easier, and takes less key strokes, and I'm all for saving work.

While Wrap as written is more versatile, it gives me the thought that you might just create a function Quote -

Function Quote(varString)
   Quote = """" & varString & """"
End Function

Select allOpen in new window

Then you could actually read what you've coded in more natural language:
strSomeVar = "Some Text" & Quote(strUserInput) & "Some more Text"

Lee,

That is actually what I started out with, in my first version.  The problem I found was that it didn't account for the possibility of embedded quotes in strUserInput.  

You know how hard it is to control user input in a text or memo field.  ;-)

That is why I added the Replace( ) function call, and then eventually modified it again to accept alternate characters as the WrapWith argument.

I'm too late for the party but I always do this.

Const QUOTE = """"

and then something like

Msgbox QUOTE & "This will appear as a string surrounded by quotes" & QUOTE

Actually, I do something similar (and would have eventually caught that) - in ASP pages, I use a function I call "SQLize" that replaces ' with ''  - SQLize(Request.Form("variable"))

Hi Guys

Since that this post is still progressing I thought that I'd run a check on the various methods suggested. They all work to various extent. However, some of them add extra quotation marks within the string. The string I used to test is:

The experts said "There"s more then one way to skin this cat.  I'm now checking their methods. All in all I say "Thanks guys for your contributions"".

I bit of a nightmare enter but you get that from users.

I attach a copy of the test db just for participation sake.
dnquotetest.accdb