Need help with macro syntax - encountering text with an apostrophe

Question

I'm running into an issue with a Macro and need help with the syntax . . .

The error is generated because for the first time there is an apostrophe in the string variable that is being used . . . In the example below, the code is fine with the Species = "Elds Deer" but has an issue with "Eld's Deer" as the species name.

The code is:

Dim StrWhere As String

StrWhere = "Species = " & "'" & strSpecies & "' and LowerBound <= " & dblTotal & " and upperbound >= " & dblTotal

CalcMedal = Nz(DLookup("Medal", "tblTGRMedalRankingMaster", StrWhere), "")

The error is: Run-time error '3075':

Syntax error (missing operator) in query expression 'Species = 'Eld's Deer' and LowerBound <= 300.5 and Upperbound >= 300.5

Answer 1

If the species names don't contain double quotes ", you can use

StrWhere = "Species = " & Chr(34) & strSpecies & Chr(34) & " and LowerBound <= " & dblTotal & " and upperbound >= " & dblTotal

Chr(34) is the " character.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

A literal quotes character can be represented by a contiguous pair of quotes characters:

StrWhere = "Species = """ & strSpecies & """ and LowerBound <= " & dblTotal & " and upperbound >= " & dblTotal

---

Ken Sheridan, Stafford, England

Answer 3

Thanks so much . . . both replies were helpful for understanding future syntax . . .

Answer 4

Thanks so much . . . both replies were helpful for understanding future syntax . . .