Trailing Spaces

[MichaelRo]

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?

 

[VicRauch]

You have found something that Access does which is truncate spaces out of text fields when that text has been written to a table, then the trailing spaces are no longer there when you re-read that text from a table. I don't know of any way to keep those spaces at the end of a text field. I can't remember this ever being a problem for me. If you could explain a bit of how this is impacting you, someone should be able to help you work out a work-around.

 

[MichaelRo]

I knew Access truncated the spaces if you write directly into a field - But it doesn't if you import to a table

How my database works is that it imports data into a load table from excel, applies lookups and allows the data to be changed before appending it to the main data table.

If the field successfully gets a lookup value it will not be validated any further.

However when any Business Objects reports are created they do not ignore the spaces as Access previously has.

I can easily include a trim either in the import routine or in BO - Just thought it strange that Access would match fields which are not a match....