none
Need help with macro syntax - encountering text with an apostrophe RRS feed

  • 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

    Tuesday, March 17, 2020 5:45 PM

Answers

  • 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)

    • Marked as answer by Big Itch Tuesday, March 17, 2020 6:34 PM
    Tuesday, March 17, 2020 5:52 PM

All replies

  • 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)

    • Marked as answer by Big Itch Tuesday, March 17, 2020 6:34 PM
    Tuesday, March 17, 2020 5:52 PM
  • 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

    Tuesday, March 17, 2020 6:14 PM
  • Thanks so much . . . both replies were helpful for understanding future syntax . . .
    Tuesday, March 17, 2020 6:35 PM
  • Thanks so much . . . both replies were helpful for understanding future syntax . . .
    Tuesday, March 17, 2020 6:35 PM