Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Chr() function

    I have this line of code in my Access database that is causing me problems due to a ' after the word Boys

    DoCmd.OpenReport "rptPIAAFormSelectedWinterSport", acViewPreview, , "tblStudentInfo.[Winter Sport] = 'Boys' Basketball' "

    I think you can fix it by using chr(39) in place of the '. I can't get the syntax correct. Can anyone help?

    Thanks Paul

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts
    I'd first create a couple of global functions which you can call from anywhere, including SQL. call them anything you want, but here are mine:

    Public function qu() as string
    qu=chr(34)
    end function

    public function sq() as string
    sq=chr(39)
    end function

    You seemed to have some extra apostrophes in your original code. I'm assuming you only need the 1 after "Boys". So I'd rewrite your code to:

    DoCmd.OpenReport "rptPIAAFormSelectedWinterSport", acViewPreview, , "tblStudentInfo.[Winter Sport] = " & qu() & "Boys' Basketball" & qu()
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reply

    That worked. Great! Thanks.

    Now how do I add another variable that would cause the report to print. I added the type in red, but it would not work.

    DoCmd.OpenReport "rptPIAAFormSelectedFallSport", acViewPreview, , "tblStudentInfo.[Fall Sport] = " & qu() & "Boys' Varsity Soccer" & qu() Or "tblStudentInfo.[Fall Sport] = " & qu() & "Boys' JV Soccer" & qu()

    Thanks again for the help.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Try: DoCmd.OpenReport "rptPIAAFormSelectedFallSport", acViewPreview, , "tblStudentInfo.[Fall Sport] = " & qu() & "Boys' Varsity Soccer" & qu() & " Or tblStudentInfo.[Fall Sport] = " & qu() & "Boys' JV Soccer" & qu()

  5. #5
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That approach worked.

    Thanks so much.

    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •