links to Oracle

[Nebuchednezzar]

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

My boss 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) in Oracle 7.x.
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?

 

[JoeMiller]

I run Pervasive SQL here on my NT server and our third party app (Solomon IV) pads all entries into the tables with spaces as well. I do extensive ODBC work and the spaces are ignored in all field types when I do searches, etc... I do things similar to your boss in A97 all the time. Access always knows to strip the spaces when searching, filtering etc.. I don't know if this helps or hurts, but I thought I'd let you know that Access can and does do it. I don't know whether this is a function of Access or the DB engine itself. I'm not sure what datatype VarChar(20) which would exclude it from working or not, so sorry I can't help there.

My $0.02

Joe Miller
joe.miller@flotech.net

 

[Nebuchednezzar]

hmmm.
thanks for the reply, but unfortunately, it doesn't help.
If it does work, I need to know why it is not working for me.
I've created a new table with a varchar2(20) field and it does the same thing. (it will not find the record when doing an advanced filter unless my criteria is "like '123456*'".