This should be simple! I have a large table with 3 numeric fields. I want to calculate a value based on these 3 fields. Then only return records where that value is <1 (say). Trouble is the calculation is too complex for access if I use just one expression. To make it easier to explain let's assume the calc I want is:
fieldx^2 + fieldy^2 + fieldz^2.
So I created a subquery that splits the calculation into 3 fields.
One is expr1:
fieldx^2, one is expr2:fieldy^2 and expr3 is fieldz^2. Then I create expr4 which is expr1 + expr2 + expr3. Then I use this subquery in another query, which works fine and I can see the field expr4 has been calculated correctly.
So I now try to only select records where expr4 <1 using
select * from subquery where expr4 <1
And I get the error "Datatype mismatch in criteria expression". Strangely when I try selecting on expr1,2 or 3 it works fine.
