8

In my current VBA code, I have a query in which I am using Chr(34) to put quotation marks between some of my variables.

I was wondering what alternatives exist to this. This is a simple question, i know, but I haven't had any success with repeating quotation marks like this

" & variable string here & "

My code is messy for one and not understandable for people who are not familiar with VBA:

comboService = Chr(34) & Me.Combo8.Value & Chr(34)

Also, this hasn't worked:

comboService = """" & Me.Combo8.Value & """"

Can you perhaps tell me why?

Thanks in advance.

brettdj
  • 54,857
  • 16
  • 114
  • 177
Paolo Bernasconi
  • 2,010
  • 11
  • 35
  • 54
  • 4
    This: `"""" & Me.Combo8.Value & """"` works in VBA. What error or problem are you getting? – RBarryYoung Nov 28 '12 at 00:54
  • Can you pls add a more detailed example. It isn't clear how many quotation marks you want to add. – brettdj Nov 28 '12 at 00:54
  • Sorry, i just corrected my mistake. I am only looking to add 1 Quotation mark. I'm using the value of the variable `comboService` in a Query. – Paolo Bernasconi Nov 28 '12 at 00:56
  • 1
    As RBarryYound posted your prior code was fine along the lines of `MsgBox """" & strIn & """"` is fine. If quote marks prove difficult then the quick workaround is to use the macro recorder to record the string you need in a formula – brettdj Nov 28 '12 at 01:00
  • Keep in mind, if you are using this in a Query, you can use single quotes instead. SQL reads single and double quotes as the same thing. Example: comboService = "'" & Combo8 & "'" (I dropped the Me. and the .Value as the Me. is assumed and the .value is the default return of a combo box) – Mike Sep 05 '13 at 15:30
  • I would argue that 4 quotation marks may be harder to understand for a non-VBA user than code that adds a specific character, along with a comment that character 34 is a quotation mark. – guitarthrower Feb 13 '15 at 20:16

3 Answers3

12

This:

comboService = """ & Me.Combo8.Value & """

is what you posted, but you need to add an extra quotation mark in order to add a literal quotation mark:

comboService = """" & Me.Combo8.Value & """"

Double-quotes within a string are what you are looking for.

aVar = "This: "" is a literal quotation mark"
transistor1
  • 2,915
  • 26
  • 42
7

I took a page from MS (the old vbCRLF) a while back, and just define any "tricky" characters I'll need as a string at the top of my code ...

Dim vbDblQuote As String
vbDblQuote = Chr(34)

Now you can just use that pseudo-constant as you build strings ...

strMyString = "Just another string " & vbDblQuote & "with quotes" & vbDblQuote & "!"

This makes code more readable, and also helps avoid "miscounted quote errors"

Liam
  • 27,717
  • 28
  • 128
  • 190
Grady Patterson
  • 71
  • 1
  • 1
0

I have found the same behavior if I output to a file using the Write command. Any double quotes get repeated.

However, if you construct the string and then output to file using the Print command, this does not happen and all works as expected.

Gary99
  • 1,750
  • 1
  • 19
  • 33
John
  • 1
  • 1