Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Storing spaces in text field (Access 2003, SP2)

    Hi,
    I need to be able to store a space, actually this " - ", in a table so that I can use it consistently when I concatenate two or more fields together in a query.
    I have "1234", "This is a Profit Centre" and I need this "1234 - This is a Profit Centre".
    I thought that I would create a single record table to contain all of these type of 'global' things and reuse them in queries as the need arises.
    Any advice gratefully accepted.
    Zinger

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Storing spaces in text field (Access 2003, SP2)

    I can't imagine why you would want to store this in a table when you're going to do a concatenation anyhow. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing spaces in text field (Access 2003, SP2)

    So that I can re-use it whenever I need to concatenate some strings, rather than typing it in every time.

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

    Re: Storing spaces in text field (Access 2003, SP2)

    The problem is that Access automatically drops trailing spaces, so if you enter " - " (without the quotes), it is stored as " -" (again, without the quotes). You could import the record from another application, for example Excel. This should preserve the trailing spaces, but as soon as you click in a field containing trailing spaces, Access will drop them, so it's not a very stable solution.

    For such a short text as " - ", it is probably more efficient to type it in. Or you could create a custom VBA function:
    <code>
    Public Function DashWithSpaces() As String
    DashWithSpaces = " - "
    End Function
    </code>
    But frankly, the expression
    <code>
    [Field A] & " - " & [Field B]
    </code>
    takes less time to type in then
    <code>
    [Field A] & DashWithSpaces() & [Field B]
    </code>
    so I don't see the advantage.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing spaces in text field (Access 2003, SP2)

    Thanks Hans,
    You're right in it taking less time to actually type the short text, so that was what we ended up doing.
    But of course Murphy being Murphy, we mistyped the short text, and then spent some time finding where the error was.
    We are working on implementing a budgeting application, where the initial loading and creation of hierarchies, structures and fields is all done through multiple Excel workbooks containing no lookups, no automatic re-use of previously created components.
    All in all it is an abomination.
    What we are doing is creating some of those 'building blocks' in Access using its 'relational' structure and 'linking' from the worktabs to the appropriate queries so that if we do find data inconsistencies, we can just refresh the workbooks after changing the underlying data in Access.
    Thanks again.
    Zinger

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Storing spaces in text field (Access 2003, SP2)

    The custom function method you described is very convenient when you are trying to build strings that must contain quotes. For example, I created a qu() function that merely returns char(34). Makes code easier to read also.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Storing spaces in text field (Access 2003, SP2)

    I would say custom functions are the way to go then, even if they just return a predefined constant. For the example you gave, something like:
    <pre>Public Function Concat(strOne as String, strTwo as string) as string
    Concat = strOne & " - " & strTwo
    End Function
    </pre>

    is fairly short.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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