Solved Access Query - Expression Builder, IIFs, Between & AND statements (1 Viewer)

S1LV3RF0X87

Registered User.
Local time
Today, 14:20
Joined
Jul 7, 2017
Messages
26
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.

1629800664043.png



Many Thanks
 

Minty

AWF VIP
Local time
Today, 21:20
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 14:20
Joined
Jul 7, 2017
Messages
26
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

Registered User.
Local time
Today, 14:20
Joined
Jul 7, 2017
Messages
26
This was the final outcome.

1629802081760.png


And as text for anyone.

Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:20
Joined
May 7, 2009
Messages
19,169
Code:
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))
 

Users who are viewing this thread

Top Bottom