Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting spaces in text string (Access 97)

    Is there a relatively easy way to convert a text string with initial caps to one that also includes spaces?

    SummaryReport would become Summary<space>Report

    Many thanks!

    --Karyl

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Inserting spaces in text string (Access 97)

    Hi Karyl -

    I'm not sure whether you would consider this approach "relatively easy" or not, but it would almost certainly involve using VBA. That might make it NOT easy, but if it doesn't cause you to <img src=/S/flee.gif border=0 alt=flee width=25 height=25>, then a shippet of VBA code that did something like this should do the trick:
    <font color=blue>
    For I=1 To Len([MyString])-1
    If ASC(Mid(MyString,I,1) > 96 then
    If ASC(Mid(MyString,I+1,1) < 91 then
    MyString=Left(MyString,I) & " " & Right(MyString,Len(MyString)-I)
    End If
    End If
    Next I
    </font color=blue>
    There are of course some subtlies not dealt with, such as how you want to handle special characters. Hope this isn't too intimidating.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting spaces in text string (Access 97)

    This will work OK if there are no spaces.
    BUT I haven

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting spaces in text string (Access 97)

    A slight improvment on the privious code:::

    This will handle strings with spaces in (Prevent code putting in two spaces)
    NOT CHECKED AND TESTED FOR ERRORS!!!! Be Warned
    ----------------------------
    Public Function fSpaceB4Caps(InputString As String)
    'Read as Space before a Capital Letter
    'Changes a string, "JoeBloggs to "Joe Bloggs"
    'This will handle strings with spaces in (Prevent code putting in two spaces)
    'NOT CHECKED AND TESTED FOR ERRORS!!!! Be Warned
    Dim intCode As Integer
    Dim intNameLen As Integer
    Dim intCheck As Integer
    Dim Flag As Boolean
    Flag = False
    fSpaceB4Caps = ""
    intNameLen = Len(InputString)
    intCode = 1
    Do Until intCode = intNameLen + 1
    intCheck = Asc(Mid(InputString, intCode, 1))
    If intCheck = 160 Then Flag = True 'If a space detected, on next itteration don't add a space
    If intCheck > 64 And intCheck < 91 Then

    If Flag = True Then
    fSpaceB4Caps = LTrim(fSpaceB4Caps & Chr(intCheck))
    Flag = False
    Else
    fSpaceB4Caps = LTrim(fSpaceB4Caps & " " & Chr(intCheck))
    End If
    Else

    fSpaceB4Caps = LTrim(fSpaceB4Caps & Chr(intCheck))

    End If
    intCode = intCode + 1
    Loop
    End Function

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting spaces in text string (Access 97)

    Thanks for the code, Wendell and Rupert (although I got Wendell's first and had already started working with it when Rupert's came, so I haven't looked at it much, yet). Wendell, you didn't scare me off. Once I figured out that & l t ; [spaces added] was supposed to be < (displayed correctly on the web site but converted in the e-mail copy I received to the strange combination of characters), it was pretty easy to understand. I just wish I had checked the website before trying--futilely--to figure out what new-to-me Access constant & l t ; was supposed to be! I also wouldn't have had such trouble if I'd looked at the logic before looking at the syntax, but it all came out right in the end. I found some obscure Microsoft article about & l t representing <, and the light dawned. (Rupert's messages also displayed the strange & l t ;, making me even more convinced that it was another Access peculiarity that I hadn't learned yet!)

    For those who might be interested, here is what I ended up with. I wanted a form with three controls, a list box and two command buttons. The list box was to display all the reports available to the user (removing "rpt" from their titles, and skipping sub reports which I prefix "rptSub" and adding back in the spaces I leave out when I name objects). The command buttons either Preview or just Print the report selected, and--what didn't occur to me when I asked how to add the spaces--the code for the buttons also takes the spaces back out again. The original code for filling the list box with the report names (without any clean-up of rpt prefixes, etc.) came from a sample form I found on one of my hard drives, probably from an old Keystone tape series. I suspect I MIGHT be able to understand that code, too, given enough time; right now, it was enough that it worked. The Row Source Type property for the list box is the FillWithReportList function.

    I can't swear I got all the tabs in the right spots, but below is the code. The parts that put in and take out the spaces are in blue.

    --Karyl

    Option Compare Database
    Option Explicit

    Function FillWithReportList(ctl As Control, vntID As Variant, _
    lngRow As Long, lngCol As Long, intCode As Integer) _
    As Variant

    Dim db As Database
    Dim cnt As Container
    Dim doc As Document
    Dim intCounter As Integer
    Static sastrReports() As String
    Static sintNumReports As Integer
    Dim varRetVal As Variant
    Dim I As Integer

    varRetVal = Null

    Select Case intCode
    Case acLBInitialize ' Initialize.
    Set db = CurrentDb
    Set cnt = db.Containers!Reports
    sintNumReports = cnt.Documents.Count
    ReDim sastrReports(sintNumReports - 1)
    <font color=blue>For Each doc In cnt.Documents
    If Left(doc.Name, 6) <> "rptsub" Then
    sastrReports(intCounter) = Right(doc.Name, (Len(doc.Name) - 3))
    For I = 1 To Len(sastrReports(intCounter)) - 1
    If Asc(Mid(sastrReports(intCounter), I, 1)) > 96 Then
    If Asc(Mid(sastrReports(intCounter), I + 1, 1)) < 91 Then
    sastrReports(intCounter) = Left(sastrReports(intCounter), I) & " " & _
    Right(sastrReports(intCounter), Len(sastrReports(intCounter)) - I)
    End If
    End If
    Next I
    intCounter = intCounter + 1
    End If
    Next doc
    varRetVal = sintNumReports</font color=blue>
    Case acLBOpen 'Open
    varRetVal = Timer 'Generate unique ID for control.
    Case acLBGetRowCount 'Get number of rows.
    varRetVal = sintNumReports
    Case acLBGetColumnCount 'Get number of columns.
    varRetVal = 1
    Case acLBGetColumnWidth 'Get column width.
    varRetVal = -1 '-1 forces use of default width.
    Case acLBGetValue 'Get the data.
    varRetVal = sastrReports(lngRow)
    End Select
    FillWithReportList = varRetVal
    End Function

    *************

    Private Sub cmdPreview_Click()
    Dim I As Integer
    Dim strReportName As String

    On Error Resume Next

    <font color=blue>strReportName = Me!lstReports.Value
    For I = 1 To Len(strReportName) - 1
    If Asc(Mid(strReportName, I, 1)) = 32 Then
    strReportName = Left(strReportName, I - 1) & Right(strReportName, Len(strReportName) - I)
    End If
    Next I</font color=blue>

    DoCmd.OpenReport "rpt" & (strReportName), acPreview

    End Sub

    *************

    Private Sub cmdPrint_Click()
    Dim I As Integer
    Dim strReportName As String

    On Error Resume Next

    <font color=blue>strReportName = Me!lstReports.Value
    For I = 1 To Len(strReportName) - 1
    If Asc(Mid(strReportName, I, 1)) = 32 Then
    strReportName = Left(strReportName, I - 1) & Right(strReportName, Len(strReportName) - I)
    End If
    Next I</font color=blue>

    DoCmd.OpenReport "rpt" & (strReportName), acNormal

    End Sub

    *************

    Private Sub lstReports_DblClick(Cancel As Integer)

    cmdPreview_Click

    End Sub

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Inserting spaces in text string (Access 97)

    Hey - well done <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> And sorry about putting the HTML [ tab ] stuff in so it would display nicely on the web. Anyhow, you've done well with a tricky little problem.
    Wendell

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Inserting spaces in text string (Access 97)

    Hi Karyl,

    Glad you got this figured out. Just some suggestions if you want to simplify matters a bit as far as populating the listbox that lists the reports:

    First add a public function to a standard module (or the form module if not needed elsewhere) with Wendell's clever code for inserting spaces:
    <pre>Public Function AddSpace(strTxt As String) As String

    Dim i As Integer
    For i = 1 To Len([strTxt]) - 1
    If Asc(Mid(strTxt, i, 1)) > 96 Then
    If Asc(Mid(strTxt, i + 1, 1)) < 91 Then
    strTxt = Left(strTxt, i) & " " & Right(strTxt, Len(strTxt) - i)
    End If
    End If
    Next i
    AddSpace = strTxt

    End Function</pre>

    Then create a query using this function, with SQL like this:

    SELECT AddSpace(Right([Name],Len([Name])-3)) AS [Report Name], MSysObjects.Name AS RptNameActual
    FROM MSysObjects
    WHERE (((MSysObjects.Type)=-32764) AND ((Left([Name],6))<>"rptSub"))
    ORDER BY AddSpace(Right([Name],Len([Name])-3));

    MSysObjects is a system table; to view, in Tools>Options "View" tab select "System Objects". I based query on report naming convention you described. Use this query as the Row Source for listbox rather than function. The second column (actual report name) will be the bound column. The attached illustration shows what query results will look like on form (in actual use, of course, the second column will be hidden, shown here for illustrative purposes only). I used a copy of the NorthWind database for test purposes.

    If you use this method you can also simplify code used to open selected report. Example:

    <pre>Private Sub Preview_btn_Click()
    PrintReport (2) 'acViewPreview
    End Sub</pre>

    <pre>Private Sub Print_btn_Click()
    PrintReport (0) 'acViewNormal
    End Sub</pre>

    <pre>Private Sub PrintReport(intOpt As Integer)
    DoCmd.OpenReport Me.List0, intOpt
    End Sub</pre>


    You may want to add error handling, etc., as necessary. Just some suggestions, to me the code used to populate listbox is more convoluted than it needs to be
    Attached Images Attached Images

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting spaces in text string (Access 97)

    Hi Mark,

    How can I get my posts to format the code correctly as per yours?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting spaces in text string (Access 97)

    See <!post=post 30349,30349>post 30349<!/post>.

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Inserting spaces in text string (Access 97)

    Hans answered your question. As a trivia note, since it may not be obvious, the HTML "pre" element, or tag, stands for pre-formatted text. It tells the browser to render characters (including spaces) as formatted in the source text, in monospace font. Maybe if the Markup Tag Panel's "ToolTips" included a short description of each tag it would be easier to know how to do this (hint....) (First time I posted some sample code I had to dust off one of my old HTML reference books to figure out which tag to use for correct formatting....)

  11. #11
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting spaces in text string (Access 97)

    Mark, thank you so much for adding this option. I like it much better, if only in that I understand it! The whole time I was working with the other code, I kept thinking: there has to be an easier way. And your suggestion is definitely easier, and one I might actually be able to remember how to do next time I need it. For this project, I don't have time to implement it, since I need to get some other stuff done before I e-mail the files off to the client later this morning, but it will definitely be part of the next revision, or next project, whichever comes first. Again, many thanks!

    --Karyl

  12. #12
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting spaces in text string (Access 97)

    Thanks, Rupert, for asking the question, and Hans and Mark (and Geoff, originally) for answering it. Another one of those "heck of a lot easier" ways to do something! I used the method. I think I was eight tabs deep at the furthest indented text, and I got totally lost trying to un-indent everything back again!

    Sure wish I had time to read this forum all the time instead of just when I hit a problem. No telling how much stuff I'd learn! Maybe when the girls are a bit older. . .

    --Karyl

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting spaces in text string (Access 97)

    Sorry!! Found an Error, line 16 corrected (Two ASCII Codes for [SPACE] 160 & 32)

    Public Function fSpaceB4Caps(InputString As String)
    'Read as Space before a Capital Letter
    'Changes a string, "JoeBloggs to "Joe Bloggs"
    'This will handle strings with spaces in (Prevent code putting in two spaces)
    'NOT CHECKED AND TESTED FOR ERRORS!!!! Be Warned
    Dim intCode As Integer
    Dim intNameLen As Integer
    Dim intCheck As Integer
    Dim Flag As Boolean
    Flag = False
    fSpaceB4Caps = ""
    intNameLen = Len(InputString)
    intCode = 1
    Do Until intCode = intNameLen + 1
    intCheck = Asc(Mid(InputString, intCode, 1))
    <font color=red>If intCheck = 160 Or intCheck = 32 Then Flag = True 'If a space detected, on next itteration don't add a space</font color=red>
    If intCheck > 64 And intCheck < 91 Then 'If a "Cap" is detected

    If Flag = True Then 'Don't add a Space, (Do add the Character)
    fSpaceB4Caps = LTrim(fSpaceB4Caps & Chr(intCheck))
    Flag = False 'Reset Flag
    Else 'Do add a Space & the Character
    fSpaceB4Caps = LTrim(fSpaceB4Caps & " " & Chr(intCheck))
    End If
    Else

    fSpaceB4Caps = LTrim(fSpaceB4Caps & Chr(intCheck))

    End If
    intCode = intCode + 1
    Loop
    End Function

Posting Permissions

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