Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Ambiguous Field Data Type - Cannot set criteria on field

    I am accessing one of our company's systems via ODBC in Access but for some reason I'm getting an error when I try to set criteria on our member number field in a query. I looked at the data type in design view and it looks like the member number is classified as text, however it does not seem to behave as text.

    Say I query a table with member field and set criteria to 512345 to look for that member. After entering, it puts quotes around the number. When I run the query then, I get the following error:
    ODBC--call failed.

    [IBM][CLI DRIVER][DB2/6000] SQL0401N The data types of the operands for the operation "=" are not compatible. SQLSTATE=42818 (#-401)
    Has anyone ever experienced this before? I'm finding myself having to either do a make table of all the records or convert the member number to a value, setting criteria on it but having to wait as it converts all records.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This can happen when the data type is translated to a text type in Access while the length is more than 255 characters in the original table. Check the length of the column on the server.
    Have a nice day!

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    It shows as having a length of 255 characters in Access in the design view of the table. Is that only what Access cuts it down to?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's possible, that's why I advised to check the definition of the column on the server.
    Have a nice day!

  5. #5
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Unfortunately I'm not authorized and I'm afraid our IT people won't know where to look. Is there no way to have Access change how the field is recognized? Even if it is more than 255 characters I won't be able to do anything about it on the server side.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Unfortunately Access won't let you change the column definition of an attached table. The only alternative that could perhaps solve your problem would consist in using a pass-through query instead of an attached table. Even a DDL SQL statement (ALTER TABLE TableName ALTER COLUMN...) would cause an error when used on a linked table.
    Have a nice day!

  7. #7
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I think I might have looked into a pass through query but for some reason it would not work in the given situation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •