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:
[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.
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.
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.