AS400 table linked into Access 2010 where clause ignored

I have a Access 2010 database that uses AS400 linked tables in queries. One field, defined as character; size 1 in AS400 is ignored in the Access where clause.  I can query on other character fields and it works fine. When I do a make table in Access and copy the AS400 table into a local table, I get the expected return from the where clause. This field has two values Y or P and I know it is case sensitive so I use caps (Y or P).  Has anyone had this happen before? Is there a way around it so I do not have to do a make table?

Thanks
LeLeBrownAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi Le,

When one of our AS/400 gurus shows up, the first thing that they're going to ask is:

What version of Access are you running?
What version of DB2 are you running?
What version of OS/400 are you running?
What driver are you running from the system running Access?


Kent
0
LeLeBrownAuthor Commented:
MS Access is 2010
AS400 and DB2 is V5R4M0  I believe
I am not sure what you mean by driver... I have a ODBC connection to the AS400 to link to the tables. Not sure if that answers that question.
0
Kent OlsenData Warehouse Architect / DBACommented:
What ODBC connector are you using?  Probably in "Data Sources" or "Machine Data Sources"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Can you post the SQL you were using?
Try making a query from the as40 table like this:

SELECT YourField
FROM YourTable
WHERE cstr(YourField)="P"
0
aikimarkCommented:
Are you writing pass-through queries or regular Access queries?
0
LeLeBrownAuthor Commented:
There is a pass-through query that create a table and the query I am trying to write is an Access query. The table returned from the pass-through query contains all records "P" and "Y" rows.
I will have to look at the ODBC data source at work. I am not sure if it is 64 or 32.
0
LeLeBrownAuthor Commented:
I refreshed the linked AS400 table in Access and nothing changed.
I deleted the table and linked it again and now it is working.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeLeBrownAuthor Commented:
No other posts were suggestions on a fix. I found the solution on my own.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.