Solved Access Query - Expression Builder, IIFs, Between & AND statements

[S1LV3RF0X87]

Hi All,

I have a bit of a headache in trying to figure out what i need to do with this query to get the outcome that i want so i hope you lot can help me out.

I have a query for where i am using the expression builder to set my iif criteria, The expression builder will be referencing another query and will be looking at a set field within that query then matching it with another field with the original query, when it finds a match it moves onto the next statement as i have multiple And statements which are all rinse and repeat but are all various different fields within the original query to reference. When it finds a match to the outcome the TRUE statement on the IIF clause will kick in and add up all the items that match the set criteria and give me my answer. (This works fine)

Now for the tricky part.

If one of the And statements when referencing the original query is blank how do i make the expression builder treat this like a wildcard and say it doesn't matter whether it is blank and to simply ignore that part of the criteria.

I was thinking something like putting the NZ() around each section of the And Statement but i have had no luck on getting that to work.

Hope someone can put me in the right direction with this one.

Many Thanks

 

[Minty]

Can you post up the expression as text please, I'm not going to try and recreate it from the picture.

I would suggest that it might be easier to write another query that gives you all those calculations, and then do your date criteria and check for null values at that point?

 

[S1LV3RF0X87]

Okay people i have managed to figure it out on my own it was just as simple as setting the criteria for the NZ([Expression],"*").

I hope this helps someone else in the future.

 

[S1LV3RF0X87]

This was the final outcome.

And as text for anyone.

Performance: Sum(IIf([Written Business]![Solution-Date_of_Sale] Between [From] And [To] And Nz([Written Business]![Solution Area]=[Solution-Area-Target],"*") And Nz([Written Business]![Product Category]=[Product-Category-Target],"*") And Nz([Written Business]![Sub-Category]=[Sub-Category-Target],"*") And Nz([Written Business]![Servive Provider]=[Service-Provider-Target],"*"),[Qty],0))

 

[arnelgp]

Performance:Sum(IIF(Nz([Written Business].[Solution-Date_of_Sale, #1/1/1890#)
Between [From] And [To] And
Nz([Written Business].[Solution Area], "@!") = Nz([Solution-Area-Target], "@!") And
Nz([Written Business].[Product Category], "@!") = Nz([Product-Category-Target],"@!") And
Nz([Written Business].[Sub-Category], "@!") = Nz([Sub-Category-Target], "@!") And
Nz([Written Business].[Service Provide], "@!") = Nz([Service-Provider-Target], "@!"),
[Qty], Null))