Results 1 to 1 of 1
2007-01-17, 17:57 #1
- Join Date
- Aug 2002
- Leuven, Vlaanderen, Belgium
- Thanked 0 Times in 0 Posts
FYI: trailing spaces issue with import & relations
Hi, fyi & fwiw (no question),
(knowing it's a bad idea to have text key fields, but in case you inherit someone else's database or other conditions justify it anyway,...)
I've learnt to be very careful regarding trailing spaces.
It looks like Access (intentionally?) doesn't properly deal with trailing spaces in referral key fields bound by relations with referential integrity.
I noticed this after I found strange duplicates in the row headers of an Access data based excel pivot table.
A table's referring key field contains a number of records with several trailing spaces at the end of the field's values.
Still, in the 'lookup table' to which this field is related, no such values exist.
These spaces could not be removed without first temporarily deleting the relations in the relationship window: otherwise I got the typical error message that editing was not allowed because a related record must exist in the related table
From a (simple) test, it looks like the existence of such values is ignored:
- when you define such a (ref int) relationship when there already exist records with trailing spaces in the referral field (like here, where the original developer hadn't defined any), Access does not generate an error of message from any kind;
- when importing new data when such a (ref int) relationship is already in place: such records are accepted (sure with copy-paste from excel, not tested with regular import). The trailing spaces remain there when you browse through the records of the table/query. Only when you edit the field itself (even only removing the last space), Access automatically deletes the spaces when you leave the record, also when you try to you 're-enter' them.
Allen Browne was so kind to address some issues related to trailing spaces here: