Trailing Spaces

MichaelRo

Well-known Member
Joined
Jun 7, 2004
Messages
549
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Well-known Member
Joined
Jun 7, 2004
Messages
549
I knew Access truncated the spaces if you write directly into a field - But it doesn't if you import to a table :confused:

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

Forum statistics

Threads
1,141,018
Messages
5,703,757
Members
421,314
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top