filter problems in Access 97 linked to Oracle 7.x database...

[Nebuchednezzar]

Ok...I'm just looking for someone to back me up here...

My boss, for some reason, loves to use Access97 to link to Oracle tables.
Recently, she has said that the advanced filter option in Access is not working properly, and therefore, the structure of our Oracle tables MUST have changed.

Here's the deal...
One table has a column with definition VARCHAR2(20).
The record in the column is a part number like '123456', except that it is right padded to 20 characters by the 3rd part app we run...so it is really '123456**************', where * is a space.

She swears that in access, when she did an advanced filter on that column, she needed only to enter the value '123456' and apply the filter and access would find it.

I can see how it would do that for a CHAR field since it could feasibly know to rtrim the value, but for a varchar, it apparently does not.

Am I missing something, or is she as crazy as I think she is?

 

[karluk]

My tests indicate that you are correct that an Access 97 advanced filter on '123456' successfully matches '123456' if it is stored in a char(20) column in Oracle, but fails to match '123456' if it is stored as varchar2(20).

That is far from saying that your boss is "crazy", however. I can think several explanations of your boss's apparently aberrant behavior, including that she's absolutely right.

1. She doesn't use Access filters very often, and misremembered how she did it before. She may have used a LIKE "123456*" operator, or something similar in her filter to get a match. If that's the case your challenge is to get her back on track tactfully. She may be crazy, but she's still your boss!

2. She remembers the filter correctly, but misremembers which column she was filtering. Maybe she previously filtered on a different column that really was char(20). The advice in option 1 still applies.

3. Maybe your application has because of some quirk padded your data with space, where it used to be unpadded. That's not really a structure change, but your boss is right that something external has affected her filters.

4. Maybe someone has altered the column from char(20) to varchar(20). Then your boss is correct that a table structure change has affected her filters.

 

[Nebuchednezzar]

Thanks for the reply...
Based on her statement that it changed within the last week, I imported the table in question from an export taken in mid May...The table structure is the same, and all the data is blank padded just like it is now.

Thanks though...I think that answers the question that access will not inherently trim padded values in a varchar column.