Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Preserve leading spaces (2000sp3)

    I'm trying to store search and replace info for cleaning data in a table. Leading and trailing spaces are an integral part of this data (the extreme example is trying to replacing multiple spaces by a single space), but Access persists in removing them from my data every time I enter it . Is there any way to prevent this behaviour?

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

    Re: Preserve leading spaces (2000sp3)

    Access always deletes trailing spaces in values you enter manually, but leaves leading spaces alone. If you handle the values in code, you could enclose the text in quotes in the table, e.g.<pre>" this "</pre>

    and remove the quotes in code when using the value.

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

    Re: Preserve leading spaces (2000sp3)

    Not sure how you are saving this information, but you may be able to save leading or trailing spaces in a text field by using the VBA Space$ or String$ functions. See my previous replies to a related question starting at:

    <!post=Re: Space filling fields (97),321106>Re: Space filling fields (97)<!/post>

    When you add leading or trailing spaces to a text field, it is hard to determine the exact number of spaces when viewing the data in Datasheet view (even if using a monospace font). Here are some examples of user-defined functions that can be used to determine the length of leading or trailing spaces in a text string:

    Public Function GetLenLeadSpace(ByVal strTxt As String)

    If Len(Trim$(strTxt)) > 0 Then
    GetLenLeadSpace = Len(RTrim$(strTxt)) - Len(Trim$(strTxt))
    Else
    GetLenLeadSpace = Len(strTxt)
    End If

    End Function

    Public Function GetLenTrailSpace(ByVal strTxt As String)
    ' Assumes if string is all spaces, then is leading:
    GetLenTrailSpace = Len(LTrim$(strTxt)) - Len(Trim$(strTxt))
    End Function
    <pre>? GetLenLeadSpace(" A B C ")
    2
    ? GetLenTrailSpace(" A B C ")
    1 </pre>

    Note if string is all spaces it is defined as "leading" rather than "trailing". Some additional functions to determine length of actual text in the field:

    Public Function GetLenTrimmedText(ByVal strTxt As String)
    GetLenTrimmedText = Len(Trim$(strTxt))
    End Function

    Public Function GetLenNonSpaceText(ByVal strTxt As String)
    GetLenNonSpaceText = Len(Replace(strTxt, Chr$(32), vbNullString, , , vbBinaryCompare))
    End Function

    Note the first function excludes leading & trailing spaces, but will include any spaces contained in the text, while the second excludes all spaces anywhere in the text string:

    <pre>? GetLenTrimmedText(" A B C ")
    5
    ? GetLenNonSpaceText(" A B C ")
    3 </pre>

    You may be able to adapt some of these ideas for your purposes.

    HTH

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Preserve leading spaces (2000sp3)

    Thanks for your input. In the meantime I'd tried another workaround - storing the data in an excel sheet and linking the table - but I'm sure that will be the least efficient of the suggestions. I'll try and time the different possibilities. Are the contents of a linked table cached locally, by the way, or requeried every time the table is accessed?

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

    Re: Preserve leading spaces (2000sp3)

    In reference to linked tables, AFAIK a linked table will reflect its current data, it is not "cached locally". I don't work with linked Excel tables often, because performance tends to be very slow, even when the XL file resides on local disk. If the XL file is stored on network, performance may be degraded further. Normally, the Excel spreadsheet will reflect any updates you make to linked table, and linked table will reflect changes made to spreadsheet. How quickly the changed data is reflected may depend on the Refresh Interval as specified in the Advanced tab (Access Options dialog). Also note limitations when working with linked XL tables, for example you cannot delete rows in Access.

    Attached sample db demonstrates how you could work with text field with leading/trailing spaces in an Access table by using form when inputting records "manually" (as opposed to importing data from another source). If interested, see frmPadText form for one possible approach. Form uses some of functions described in previous reply to show how many leading/trailing spaces exist in current record. Unbound textboxes are used to update records using a simple function:

    Public Function PadText(ByRef lngLead As Long, _
    ByRef strTxt As String, _
    ByRef lngTrail As Long) As String
    PadText = Space$(lngLead) & strTxt & Space$(lngTrail)
    End Function

    There are textboxes to enter length of leading & trailing spaces, and actual text. Save button calls PadText function to update record using textbox values as parameters. These functions can also be used in update queries, etc. Note than when I import data from fixed-width FoxPro tables, the trailing spaces are not truncated (I have to use Trim function to get rid of them). If you are using data saved in the linked XL file for some sort of text parsing or validation function, you may anticipate slow performance. You'll have to experiment to determine most efficient approach to accomplish task.

    Another option is to explore the use of Regular Expressions (RegEx) for a more sophisticated and powerful approach to text validation. However, there is no RegEx engine built into Access or VBA 6.0 (there are RegEx classes built into VB.Net, but that isn't useful here). The only way to use RegEx in Access would be by setting a reference to the Microsoft VBScript Regular Expressions X.X (version no) type library (typical path, C:WINDOWSSystem32vbscript.dll3).

    HTH
    Attached Files Attached Files

Posting Permissions

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