Results 1 to 13 of 13
  1. #1
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27

    Complicated white space question

    Hey there,



    I have an excel spreadsheet with varying fields (Name, Address, Phone Number, etc...) Each of these fields obviously varies in total characters in the field. My question:

    Regardless of the number of total characters in the field, once imported I need access to add WHITE SPACE (Additional Spacing or Empty Character Space) to reflect that the field has whatever amount of Characters I specify. So for example, my NAME field needs to have a total of 20 Character Size but names such as Brown and Smith have only 5...I need to add 17 SPACES or WHITE SPACE behind that. Thank you very much for the suggestions or any help. My phone number is 614.254.9587 if you need any clarification.

    Sincerely,

    Raymond

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you looked at the Space() VBA function?

  3. #3
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    Yeah I did look at that but couldn't quite understand it. I was just looking for a simple example that I could build off of I think. I appreciate your help and quick response. Anything you can provide me with I'd be super appreciative of or any direction you could push me in to find what I need. Thanks again!

  4. #4
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Space(ByVal lngvariable) so whatever number put between the () is the number of spaces. for your purposes you would add 20 - Length of your string Spaces to your field. Something likeMyfield = myfield & Space(20-len(myfield)) If in a query take out the myfield - part of that statement and put the rest in a column.

  5. #5
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Oops that should have been myfield = part of the statement not myfield-

  6. #6
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    Awesome! I'm going to go try it now. I am importing information from excel into the tables because I didn't find anything that said excel could do this, so someone suggested access. So I should import to the table, create a query based off that table, run sql on that field and export back out. The company wants all the fields specific to size for each field so I'm sure this will help me. Thanks a lot and I'll let you know how it goes...not necessarily if I feel it worked because it will more than likely be something I did wrong! Still learning.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Trailing spaces are often irrelevant and keep in mind that Excel knows VBA as well.

  8. #8
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    yeah i know but they want it like this...and they want the spaces to be there for some reason...something to do with their old program they use.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    As I said, Excel knows VBA as well and can probably add the spaces without using Access.

  10. #10
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    I know and thanks a lot, seriously! Problem is, I have no idea how to use Access and wouldn't know where to begin. I couldn't even get this to work. I did get it to a text file by simply using the export-without formatting and then manually typing in how wide the character field should be. In the text file it has the required number of spaces but I'm still having a rough time importing into excel and having those spaces show up. Thanks for the time you've taken! I'll continue working on it...HA!

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Excel may simply not allow trailing spaces to save space. I don't know enough about Excel to offer a suggestion.

  12. #12
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    Let me ask you this, I was able to export the file as a text file with the proper trailing spaces behind them...Anything that can be done beyond that point? Like an import back in?

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It might work if you put the Spaces *Before* the value. Leading spaces are not as irrelevant.

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

Similar Threads

  1. Remove White Space
    By jsimard in forum Reports
    Replies: 3
    Last Post: 01-24-2012, 12:16 PM
  2. Replies: 8
    Last Post: 08-12-2011, 11:55 AM
  3. Why The White Box?
    By orcinus in forum Access
    Replies: 3
    Last Post: 08-05-2011, 01:55 PM
  4. Subform showing white
    By Rick West in forum Forms
    Replies: 2
    Last Post: 06-24-2010, 08:07 AM
  5. Export to RTF is creating white space at end of page
    By Sherri726 in forum Import/Export Data
    Replies: 0
    Last Post: 12-19-2006, 03:24 PM

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