MS-Access - data type mismatch in a query

Question

I have one table ("Organisations") where the said field ("ParentOrganisation") is a short text type (all records are three or four character A-Z strings. Now there's another table ("Invoices") where one field ("Organisation") is done by a Lookup Wizard pointing to "ParentOrganisation" in "Organisations" so I have a dropdown box to choose the organisation. In the properties for "Organisation" the field is classified as Number type... Why?

I have created a simple query based on the "Invoices" table including the "Organisation" field. Now when I want to specify the criteria in the design view in the query to specify ="PRT" or any other organisation, I get the error: Data Type mismatch in criteria expression. I guess it's related to the fact that the lookup field for some reason gets classified as Number not text.

Please advise

This is why I never set lookups in table design - I want to see the actual values not the lookup alias. I build comboboxes on forms. Organisation field is saving the numeric ID value from table Organisations. — June7
– June7, Commented Jun 13, 2017 at 0:54

Erik A · Accepted Answer · 2017-06-13 07:57:25Z

This is the nature of lookup fields.In Organisations.Organisation, only the primary ID of the corresponding organisation found in ParentOrganisations is saved.

To filter the Organisations table per organisation, you will need to join in the ParentOrganisations table.

Your result SQL will look something like this (to get it more accurate, you will need to share your code and table structures)

SELECT Organisations.Something FROM Organisations INNER JOIN ParentOrganisation ON ParentOrganisation.ID = Organisations.Organisation WHERE ParentOrganisation.Organisation = "PRT"

Collectives™ on Stack Overflow

MS-Access - data type mismatch in a query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related