AS400 table linked into Access 2010 where clause ignored

Posted on 2014-08-15
Last Modified: 2014-08-23
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?

Question by:LeLeBrown
    LVL 45

    Expert Comment

    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?


    Author Comment

    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.
    LVL 45

    Expert Comment

    What ODBC connector are you using?  Probably in "Data Sources" or "Machine Data Sources"
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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"
    LVL 44

    Expert Comment

    Are you writing pass-through queries or regular Access queries?

    Author Comment

    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.

    Accepted Solution

    I refreshed the linked AS400 table in Access and nothing changed.
    I deleted the table and linked it again and now it is working.

    Author Closing Comment

    No other posts were suggestions on a fix. I found the solution on my own.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now