typed data value not recognized

[lscheer]

I'm experiencing an interesting problem with a database I have been working on. For some reason, when I tried to enforce referential integrity between two fields, whose values should have matched perfectly, I got an error that I couldn't because they didn't match. When I ran an unmatched query, I found that the numbers DID match, but for some reason Access wasn't recognizing it. Even when I type the values into the field to "correct" it, it doesn't recognize it; I have to copy and paste from the master table into the child table in order for the values to be recognized as matching.

The same thing happens when I try to filter or query the table; if I type the value, it says no records are found, when I copy and paste, or select from the combo box, it works.

ANY IDEAS?? this is really weird and it's happening on multiple computers so it's not just one system. And, this has never happened before with this file or any other that I've worked on...

 

[gemma-the-husky]

are you sure one is not defined as a number, and the other as text

or maybe defined as different number sub types - eg long/integer etc

 

[lscheer]

No, sorry, I should have indicated that: both fields are TEXT fields and have the same properties set

 

[Pat Hartman]

Are the fields filled with characters or is there some "empty" space at the end? Often data that comes from another application has text fields that are padded to their full length with spaces. Fields that look identical wouldn't match in that case because space is a valid character and it counts!

 

[lscheer]

That must be what's going on, even though there didn't appear to be any special characters, linebreaks or spaces, because when I re-type the values into the master table, they are recognized by the child table.

Thanks for your help!

 

[Pat Hartman]

To get rid of the leading/trailing spaces use an update query and use the Trim() function. In the UpdateTo cell put:

Trim([yourfieldname])

Don't leave out the square brackets!!!!! Access will convert your field name to a literal and change all values to that single string. I know I don't need to remind you but before you run an update query of this nature, be sure to back up your database.