• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Any problems with nested expressions?

Hello - is there a hard & fast rule about nested expressions in Access (2010) queries, like:
Sum(1) As CountOfTotalTickets, Sum(IIf([Hours] > 0,1,0)) As CountOfTicketsWithHours, [CountOfTicketsWithHours]/[CountOfTotalTickets] As PctTicketsWithHours

I know I could do this in a single expression, but this is just one simple example from a large set of qry's I have to work up where it gets more complicated than this.

Also, I'm trying to structure these qry's in a way that I can pull them up in datasheet view and demo that the math is being done correctly (a "show your  work" kind of thing)

On occasion, I have done this with no problems, and other times, it would get flaky on me.  

Thanks
0
mlagrange
Asked:
mlagrange
2 Solutions
 
Jack LeachProprietorCommented:
No issues other than readability.  Because of readability, sometimes things get a little flaky (especially with the query designer or SQL view, as the fonts are horrible for this type of stuff).

I tend to build SQL and expressions in Notepad++ using Poor Man's T-SQL formatter, then drop them into the SQL View of a query.  Having highlighting for opening/closing brackets and being able to see things monospaced and multi-line is a huge help for stuff like this.

As far as processing goes though, nope.  Just make sure your logic and arguments are correct.

Cheers,
-jack
0
 
JimFiveCommented:
If there is any ambiguity due to the names used in the calculation then it won't work.  It is safer to always just redo the calculation (Sum(1)/Sum(iif(hours>0,1,0))), or do the final calculation in the report/form control.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now