Criteria not working with [Calculated_Values] in Access query
Posted on 2014-08-05
So I make a query, and in it I have a formula:
Small_Pets: [Total_Pets] - [Dogs]
Then I use that field later with another formula:
Cats: [Small_Pets] - [Fish]
And all of that works great. Query runs fast and all numbers show up correctly. However... Once I try to assign some criteria to the second formula everything goes to crap.
And when I run the query now a popup window asks me to input what [Small_Pets] is?
Undaunted I tried something different. Maybe if I had a text and used a different column it would work? So I tried this:
Is_Crazy: IIF([Cats]>3,"CRAZY CAT LADY!","Totally normal person")
Criteria: "CRAZY CAT LADY"
It still didn't work, it's still asking me what [Small_pets] is, even though it works normally without a criteria.
Heroically I pushed on. I thought that since this is going into a report I would use an imbedded query in the report that feeds off the first query I made. The only reason I'd need the imbedded query would be to add this tricky criteria in such a way that [Small_Pets] would be contained in the first query, and thus it couldn't ask me for it again! Brilliant, I know.
[*.Pet_Calculator] goes into imbedded query.
[Cats.Pet_Calculator] goes into a column
And when I run that it crashes my access...
So I give up. What painfully obvious simple thing am I missing here? Why won't this work?