Trailing Spaces

[MichaelRo1]

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 manually 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.

 

[PGoRule]

I think Access removes trailing spaces when you enter data directly into Access table. If the data is coming from Import, then it removes those spaces while querying that data. It ignores the case too in such situation.
To get rid of this situation you can use:
Select Field1, Field2 from Table1 where Field1 like 'A '. This will return the record matching 'A ' only.

Sharing the best from my side...

--Prashant--

 

[mp9]

search Access VBA help for Trim and LTrim functions.

E.g.

Trim("A ") returns "A"

http://www22.brinkster.com/accessory/