VBA/SQL: Nested Query, Baffling Data Mismatch

0
votes

The basic idea: I want a list of all of the processes that exist within a certain division. Processes are related to OperationalUnits, which are related to Divisions.

The query I want:

SELECT ID, ProcessName FROM Processes WHERE Unit IN (SELECT ID FROM OperationalUnits WHERE Division='1');

This always throws me a "type mismatch in expression" error.

I've tested by breaking the query down into the two component queries:

SELECT ID FROM OperationalUnits WHERE Division='1';

This returns a single ID, 2, which is what I expect from my test data.

SELECT ID, ProcessName FROM Processes WHERE Unit IN ('2');

This returns a single process, which is again exactly what I expect.

But if I combine them back together into the main query? Nope. It appears that the issue is that the inside query returns [2], while the outside query only works if it returns ['2'], but that doesn't seem like it can be right to me. Any help is appreciated!

2
sqlms-access
What are your datatypes? Processes.Unit vs. OperationalUnits.ID? - n8wrl
Processes.Unit is datatype 'Text,' while OperationalUnits.ID is datatype 'AutoNumber.' I had thought that since Processes.Unit's bound column is OperationalUnits.ID, that datatype would pass through...but perhaps not? Most of these relational fields in my database are two-column queries that are bound to ID of the source table but display the text name. This keeps it easier if I need to manually modify fields without looking up what '3' means. - Daniel Rohn
Obviously a text field, Processes.Unit, cannot be in an AutoNumber range, OperationalUnits.ID. You will need to do some type casting. Your schema is messed up also, if you are relating a text field to an AutoNumberfield. - nicomp
Yes, change Processes.Unit to type Long. - Gustav

2 Answers

1
votes

Access doesn't automatically cast types, unless in specific situations. As discussed in the comments, since your fields have different types, you can do two things: cast using a function, or change the type.

To cast, you can use the following:

SELECT ID, ProcessName FROM Processes WHERE Unit IN (SELECT CStr(ID) FROM OperationalUnits WHERE Division='1');
0
votes

You could change the data type within the SQL, although it would be better to have matching data types to start with.

Access won't be able to represent the join in Design View, but it will execute it just fine:

SELECT  Processes.ID
        , ProcessName
FROM    Processes INNER JOIN OperationalUnits ON CLNG(Processes.Unit) = OperationalUnits.ID