Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    Access 2000, no Service Packs, MDAC 2.7 installed.

    In an Access 2000 database I have created a new table. This table contains a text field of length 10.
    Irrespective of the text I enter into this field it is always padded out to 10 characters with spaces in the table.
    Older tables with text fields do not exhibit this behaviour.
    Does anyone know why Access does this (I suspect something in MDAC 2.7) and preferably how to stop it doing this.

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

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    How are you verifying that Access is actually storing the spaces in the table?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    That sounds like something I've seen in SQL Server. If you use one of the string data types (either char or nchar) the same thing happens.

    I can't remember exactly which of those does it. If you use varchar or nvarchar it will fix the problem.

    I'm not sure how this will help with Access or MDAC 2.7, but maybe it will get you on the right track... Maybe Microsoft is trying to make Access work more like SQL Server???

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    Did you check the format of the data field in the Table design view?
    Regards,

    Gary
    (It's been a while!)

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    Yes, no formatting set.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    I found the problem when I opened the table and clicked with the mouse, the cursor didn't appear after the 'last' char but further out in the field.
    I then constructed a query using len(<field>) and each record was 10 characters long, even if I just entered for example 'a'.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    What is really puzzling me is that this behaviour only appears with a newly created table. Older tables in the same database behave correctly.

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

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    That is indeed odd. I haven't heard anything about this myself, perhaps checking the MSKB might help?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    Thanks, I've cracked it.
    I had created the table via code and used an incorrect attribute which forced the field to always be 10 chars long. Surprisingly this does not appear as a field property when one looks in design mode at the table. That makes me wonder what else is possible but hidden away behind the scenes. If I have the time I'll have a play around - I've always wanted to be able to manipulate autonumber fields with default/incrementation (like SQL server / MSDE).

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

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    What was the incorrect attribute you used?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text field padding with spaces (Access 2000, No SP, MDAC 2.7)

    I used (C++ code, MFC Dao class)

    dbFixedField | dbUpdatableField

    instead of
    dbVariableField | dbUpdatableField

Posting Permissions

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