Does MS Access support "CASE WHEN" clause if connect with ODBC?
Since you are using Access to compose the query, you have to stick to Access's version of SQL.
To choose between several different return values, use the switch() function. So to translate and extend your example a bit:
select switch( age > 40, 4, age > 25, 3, age > 20, 2, age > 10, 1, true, 0) from demoThe 'true' case is the default one. If you don't have it and none of the other cases match, the function will return null.
The Office website has documentation on this but their example syntax is VBA and it's also wrong. I've given them feedback on this but you should be fine following the above example.