Query Error multivalued fields and db fields aren't multivalued

[Arnold]

Hi
I have an access db that is linked to a sharepoint list (the list originated in Access).
I tried a simple select query with parameters to search for by id and another query of the same table to search by date.
They both come back with the same error of "cannot reference a table with a multivalued field using an IN clause that refers to another database"
Iv'e gone through the table and these fields that the query is using and none seem to have any multivalues.

Anyone encounter this?
Can this have something to do that its linked to sharepoint?

[rpeare]

http://office.microsoft.com/en-us/ac...001233722.aspx

Does the sharepoint database have a CHOICE field?

Do you have access to the sharepoint database itself? can you examine the design of the tables on the 'back end'?

I suspect it has to do with the design of the sharepoint side tables, NOT how you have linked them into your database.

[Arnold]

Thank you for replying
The list originated on my computer in access and the fields that I am trying to query don't have any choices.
Is there any reason to think it changed on upload?

[rpeare]

I don't have any experience with sharepoint and creating web databases, but problems with multi value fields are common and I do not know why MS allows them at all. If you designed the database on the sharepoint site and didn't include any multi value fields or calculated value fields I am not sure what to do, I would think there is some flaw in the table design of the database on the sharepoint site. However you may be able to rework your query so it doesn't use the IN clause, but I would look at the field you're examining with the IN statement, that is likely the problem field.

[Arnold]

Thank you again
The problem is that there is absolutely no IN clause in the query.

[rpeare]

How about this,

make a copy of your database, instead of linking the tables, import them from the source then run your stuff and see what happens.

What is the SQL of your query?

You can also try rebuilding your query step by step, adding one field/table at a time until it breaks that should give you a good clue as to where the problem lies.

[Arnold]

Thanks
I will try working on this.