query only returns values if criteria cut-and-paste

[Guest]

using Access 2002 SP3 at work. I am finding that queries are not returning
records unless the criteria I enter are cut-and-paste from underlyng table.
For example, I need records from an "Activity" table, of the Type "Customer
Approval". If I simply type "Customer Approval", I get nothing. If I cut
"Customer Approval" from the underlying table, and then paste that into the
criteria, I get what I'm looking for. I've had a love-hate relationship with
Access for 10 years. This is the first time I've ever come across this.
There are no spaces or other characters, e.g., carriage returns, in the
underlying data that I can see. Ring any bells for anyone? Thanks.

 

[[MVP] S.Clark]

Perhaps there is a special character embedded in the data, that you can't
see.

You can create a Status lookup table, which has an autonumber as the primary
key. Then store the Long Integer value. Then instead of searching for
text, you could use a number.

 

[John Vinson]

using Access 2002 SP3 at work. I am finding that queries are not returning
records unless the criteria I enter are cut-and-paste from underlyng table.
For example, I need records from an "Activity" table, of the Type "Customer
Approval". If I simply type "Customer Approval", I get nothing. If I cut
"Customer Approval" from the underlying table, and then paste that into the
criteria, I get what I'm looking for. I've had a love-hate relationship with
Access for 10 years. This is the first time I've ever come across this.
There are no spaces or other characters, e.g., carriage returns, in the
underlying data that I can see. Ring any bells for anyone? Thanks.

My guess is that you have fallen victim to Microsoft's abominable
"Lookup Field" misfeature. Is the Type field a Lookup? If so, what you
SEE is the looked-up text "Customer Approval", but what's actually
stored in the table (and required as a criterion) is the numeric ID of
that record in the lookup table.

John W. Vinson[MVP]

 

[Guest]

I apologize for the delay in getting back to you. I'm afraid this is not
what's going on here. But, it's an interesting thought.

 

[Guest]

Thanks, Steve. There are no hidden characters that I can tell. Querying for
fieldlength doesn't indicate anything. Perhaps I'll simply concede defeat
and use the lookup table. I appreciate the thought and effort to get back to
me.