paulceaston
asked on
"Type mismatch in expression" error when running query using two joined linked Sharepoint datasheets
I am getting a "Type mismatch in expression" error when running query using two joined linked Sharepoint datasheets.
One datasheet is "Custodians". It is hosted on a Sharepoint server.
The other datasheet is "BoxTracker". It is also hosted on a Sharepoint server.
The BoxTracker datasheet has a lookup field, titled "Custodian" linked to the "Full Name" field of the Custodians datasheet. This is the only link between the two datasheets in Sharepoint and also in Access as shown in the Relationships view and in the query design view when both tables are shown.
Both datasheets are linked to the MS Access database I am building. I have query that show selected fields from both datasheets.
Whenever I try to run the query I get the "Type mismatch..." error. There is nothing in sharepoint to set the "type" for a lookup field. I would assume that the type would be the same as the filed it was looking up the data from.
Any help would be greatly appreciated. While I don't think this problem is "Extremely Difficult", it is urgent.
Thank you in advance!
One datasheet is "Custodians". It is hosted on a Sharepoint server.
The other datasheet is "BoxTracker". It is also hosted on a Sharepoint server.
The BoxTracker datasheet has a lookup field, titled "Custodian" linked to the "Full Name" field of the Custodians datasheet. This is the only link between the two datasheets in Sharepoint and also in Access as shown in the Relationships view and in the query design view when both tables are shown.
Both datasheets are linked to the MS Access database I am building. I have query that show selected fields from both datasheets.
Whenever I try to run the query I get the "Type mismatch..." error. There is nothing in sharepoint to set the "type" for a lookup field. I would assume that the type would be the same as the filed it was looking up the data from.
Any help would be greatly appreciated. While I don't think this problem is "Extremely Difficult", it is urgent.
Thank you in advance!
Hi Paul
Try linking the tables to your access database and then opening in design view top see the field properties. Whilst you cannot change the properties lets check the basics and that the two fields are the compatable ie the types are the same and that if number fields that they are the same types.
Rohit
Try linking the tables to your access database and then opening in design view top see the field properties. Whilst you cannot change the properties lets check the basics and that the two fields are the compatable ie the types are the same and that if number fields that they are the same types.
Rohit
>The BoxTracker datasheet has a lookup field, titled "Custodian" linked to the "Full Name" field of the Custodians datasheet.
You see, that might indicate the problem. My hunch is that boxtracker should be linked to the primary key of the Custodian table, not to Full Name. My hunch is that the boxtracker table contains a numeric field and you get the error because you are trying to join to a text field.
Of course, this is all conjecture, if you reveal the relevant parts of your table structures we can answer more confidently.
You see, that might indicate the problem. My hunch is that boxtracker should be linked to the primary key of the Custodian table, not to Full Name. My hunch is that the boxtracker table contains a numeric field and you get the error because you are trying to join to a text field.
Of course, this is all conjecture, if you reveal the relevant parts of your table structures we can answer more confidently.
ASKER
Ahhhh...light bulb goes on. I opened the BoxTracker linked table in design view in Access. The Custodian lookup field DataType is given as Number.
So I should link to the sharepoint autonumbered "ID" column? But the relationship is autmatically created when I link the tables.
So I should link to the sharepoint autonumbered "ID" column? But the relationship is autmatically created when I link the tables.
ASKER
So how do I work around this? I want to keep the look up field to ensure that anyone adding record in the Sharepoint "BoxTracker" list can select from the list of custodians in the box tracker's "Custodian" field. This ensures consistent spelling, avoids, typos, etc.
But if I use the lookup field, in Sharepoint, then I can not create queries from the linked tables in MS Access.
My goal for this query is to show all completely reviewed boxes by custodian. I'm using an Access database that I've created in the past and am replacing all the tables with linked Sharepoint lists/datasheets.
So far this is working very well with the exception of sharepoint lookup fields.
But if I use the lookup field, in Sharepoint, then I can not create queries from the linked tables in MS Access.
My goal for this query is to show all completely reviewed boxes by custodian. I'm using an Access database that I've created in the past and am replacing all the tables with linked Sharepoint lists/datasheets.
So far this is working very well with the exception of sharepoint lookup fields.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What are you storing in the table for your lookup field?
Note - what you are storing is not necessarily the same as what you can see. That is the problem with lookup fields.
Pete