Add trailing spaces to a field

Posted on 2008-06-12
Medium Priority
Last Modified: 2010-04-21
How to I add trailing sapces to a field that will be exported to a .csv file
Question by:morang
  • 3
  • 2
LVL 44

Expert Comment

ID: 21772583
You can only add trailing spaces using a query:

Update myTable set myFld = myFld & "   ";

three spaces for example

Author Comment

ID: 21772600
will that work if the number of spaces is not the same for every record?
LVL 44

Accepted Solution

GRayL earned 2000 total points
ID: 21772648
If you have a length you want to create then

UPDATE myTable SET myFld = Trim(myFld) & Space(30 - Len(Trim(myFld)));
-----------------------------------------------------------------^-- length of field with trailing spaces

In no case can the actual trimmed field length exceed the length you set.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Closing Comment

ID: 31466685
Thank you for your help

Expert Comment

ID: 21772743
rather than writing to the table, you can also create a query that adds the spaces dynamically.

- Create a new query
- Add all the fields that you'd like exported
- replace the field(s) where you need trailing spaces with
     myFld:table.myFld & space(number of spaces)
- export new query

That way you're never writing to your original data
LVL 44

Expert Comment

ID: 21772806
morang:  What bmoabc makes a lot of sense. Rather than the update to your table turn the update into a normal select query and export the the query.

bmoabc:  morang had accepted a minute before your post arrived.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question