Leading space in text field

[DanR]

Hello

I have a text field RawName which is indexed as 'no duplicates'

In some cases, my program tries to add a new record with text in RawName that is identical to an existing record, except that it has a space before the first letter. It recieves the error that this would create a duplicate entry.

For example:

Existing: "Aciagrion fragilis"
New: " Aciagrion fragilis" - not accepted

However, when performing a join these text strings are not treated as identical, so I can't simply treat these two values as equivalent.

Can someone explain the underlying rules in how Access handles text values like these, and perhaps what I could do about it.

I am reluctant to strip all leading spaces from my data (unless there is no alternative) because I already have many other records which do match on a text string with a leading space. In those cases the same string minus leading space is not in the table, hence no problem with duplicate entries.

thanks
Dan

 

[a.sinatra]

Why are you using your names as PK's? If you didnt this wouldnt of happened.

Access stops the space, but you can add a space when you edit the record.
________
Persian recipes

 

[DanR]

I'm not

I'm not using the name as primary key.

We recieve data from many sources, which are essentially lists of species (eg animals, plants) identified by a name which is a text field. This table provides a lookup to match the variety of names in the data we recieve with the unique identifier that we use for the species. So the purpose of this table is to translate from the outside world where species are known by a name, to our database where species are known by a numeric unique identifier.

And there's no way I'll be manually editing each record. There are over 70,000 records so far and I'm looking for a foolproof way for the relevant form to add a text string to the table which will match the name in the data we recieve.

 

[neileg]

It's not quite clear what the problem is here. If you want to match the two names you can use Trim() which will remove all spurious spaces including a leading space. Does this help? You can then leave the data the way it is, but any time you want to manipulate it, use Trim() to get a known value.

Access will stop you adding duplicates if the duplicate happens in a Primary Key, or you have set the field to Indexed, No Duplicates. It's not obvious if removing this would be a problem.

I think you need to re-examine your structure, if you are allowing duplicates in your data, only distinguished by a leading space. This has to be a recipie for problems, as you are finding!

 

[DanR]

Thanks Neileg

Allowing duplicate values is not an option, but the Trim function may be just the thing, so long as it doesn't unreasonably slow down large queries.

I'll give it a try...