Results 1 to 8 of 8
  1. #1
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171

    Removing apostrophe's from text in Report title

    I am passing the multiple selection criteria on a form to a textbox on the form. When the report runs I capture the selection criteria from the textbox to display it in the report title so you will know what criteria is in the report.



    Attached is picture of how it is displaying. Is there any way to remove the single quotes from each of the selections so that the report title is easier to read?

    I have looked up a lot of code but is only shows how to Replace it, I want to remove it if possible.

    I am not sure if the code goes in the form for the textbox or in the report where the textbox is displayed.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You should be able to use Replace() and replace with an empty string ("").
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm thinking you might find this disrupts your ability to display the concatenation because using Replace will make it a calculated control, which can't be bound to anything. If that happens, post back with details on how the single apostophes are included/get there in the first place. It might be better to eliminate them at the source.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I added the code where I thought it should go... it is not working there. I am still seeing the apostrophe in the report title. Should the Replace code be on the textbox on the form or on the textbox in the report title.

    txtlaborCat on the report has Control Source:[Forms]![frmPerfIssuesMain]![txtLaborCat]

    Hope this helps.

    Code:
    Private Sub cmdPerfIssuesLaborCatRpt_Click()
    On Error GoTo Err_cmdPerfIssuesLaborCatRpt_Click
      Dim strWhere      As String
      Dim ctl           As Control
      Dim varItem       As Variant
      'make sure a selection has been made
      If Me.lstLaborCatSelection.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 employee"
        Exit Sub
      End If
      'add selected values to string
      Set ctl = Me.lstLaborCatSelection
      For Each varItem In ctl.ItemsSelected
            strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
      Next varItem
      Debug.Print strWhere
      'trim trailing comma
      strWhere = Left(strWhere, Len(strWhere) - 1)
      
      'open the report, restricted to the selected items
      Me.txtLaborCat = strWhere
     
     ' replaces a single quote with empty string
       strWhere = Replace(strWhere, "", "")
      
      DoCmd.OpenReport "rptPerfIssuesbyLaborCat", acPreview, , "LaborCat IN (" & strWhere & ")"
      
      
    Exit_cmdPerfIssuesLaborCatRpt_Click:
      Exit Sub
    Err_cmdPerfIssuesLaborCatRpt_Click:
      MsgBox Err.Description
      Resume Exit_cmdPerfIssuesLaborCatRpt_Click
        
    End Sub

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You forgot the apostrophe!
    Code:
    strWhere=Replace(strWhere,"'","")
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would not do it where you have it, that will affect the OpenReport. I'd do it here:

    Me.txtLaborCat = Replace(strWhere,"'","")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Works Perfectly!!! Thanks again for all your help!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-16-2017, 03:38 PM
  2. Replies: 1
    Last Post: 07-07-2016, 02:34 AM
  3. Null parameter text in report title
    By Beeblebrox in forum Reports
    Replies: 3
    Last Post: 06-19-2016, 11:10 AM
  4. Replies: 7
    Last Post: 02-23-2014, 06:06 PM
  5. Replies: 4
    Last Post: 10-15-2012, 11:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums