Results 1 to 3 of 3

Thread: Trailing Spaces

  1. #1
    Join Date
    Jan 2006

    Unanswered: Trailing Spaces

    I always assumed that in Access when you join a field only exact matches return (Unless you change the Join Type obviously!!)

    However when I joined two tables I got unexpected results.

    So after running a quick test, I'm even more confused.

    Access matches even if there are trailing spaces (White?) after Text.

    Eg Table 1 containing Data "A", "A " & "A " linked to a 'lookup Table' with only "A" in it you would expect only 1 record to be returned - But all 3 come back????

    Am I being a bit simple or am I missing something?

    PS: I realise you cannot manaully enter the data as in my above example as Access automatically truncates the text - But if you paste or import via excel it will accept them.

  2. #2
    Join Date
    Feb 2002
    I'm not sure why it works that way, but you should be able to use the trim function.

    like on one side of the join have the field =trim(fieldname) or Rtrim for trailing spaces only or Ltrim for leading spaces only.

  3. #3
    Join Date
    Feb 2004
    One Flump in One Place
    Yes - that is how it works. The reason (I would speculate) is because many RDBMS systems use fixed length character data types (Access excluded) which result in trailing characters being "filled" with spaces. As such - you would want these to match with values in other variable length fields.

    Do these spaces really have meaning and differentiate one value from another? So "A" has a different meaning to your application to "A "? You may wish to reconsider your coding. Alternatively you will have to do something to differentiate the two values.
    pootle flump
    ur codings are working excelent.

Posting Permissions

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