I have a rather complicated calculation query designed to calculate the estimated cost of a Guided Tour based on the estimated number of children, adults, free chaperones and what type of Cost Category it is (Full Price, Discount, etc. or one of the several BOCES programs that we have unique agreements with).
The calculation query works fine and here is the SQL code in it's entirety:
The problem, and my question, comes in towards the end of all those IIf statesments in this section:
I need to find a way for the query to automatically set the Estimated_Adults - BOCES_Chaperones to 0 if it is a negative number (in other words, if they can have up to 5 chaperones free of charge and they bring only 4, I want that part of the calculation to spit out 0 instead of -1).
I have found threads on how to do this, but the solutions are to default the results of an entire calculation to 0 if it's a negative, not default a section of the calculation. Such as this: https://access-programmers.co.uk/forums/showthread.php?t=132640
Before I start banging my head against a wall, trying to make things fit, I was wondering if anyone had any further suggestions.
Thanks.
The calculation query works fine and here is the SQL code in it's entirety:
Code:
SELECT [Event Information].Event_ID, [Event Information].Program_Code, [Event Information].BOCES_PO, [Event Information].BOCES_Number_of_Participants, [Event Information].Estimated_Youth_or_Scouts, [Event Information].[Estimated_Adults-Other], [Event Information].Cost_Per_Person, [Event Information].BOCES_Chaperones, [Event Information].Canceled, [Event Information].Cancel_Fee, (IIf([Cost_Category]="Full Price",(((Nz([Estimated_Youth_or_Scouts])+Nz([Estimated_Adults-Other]))*Nz(Cost_Per_Person))+Nz([Cancel_Fee])),IIf([Cost_Category]="Discount",(((Nz([Estimated_Youth_or_Scouts])+Nz([Estimated_Adults-Other]))*Nz(Cost_Per_Person))+Nz([Cancel_Fee])),IIf([Cost_Category]="Swap",(((Nz([Estimated_Youth_or_Scouts])+Nz([Estimated_Adults-Other]))*Nz(Cost_Per_Person))+Nz([Cancel_Fee])),IIf([Cost_Category]="Donation",(((Nz([Estimated_Youth_or_Scouts])+Nz([Estimated_Adults-Other]))*Nz(Cost_Per_Person))+Nz([Cancel_Fee])),IIf([Cost_Category]="TST BOCES",(((Nz([Estimated_Youth_or_Scouts])+Nz([Estimated_Adults-Other]))*Nz(Cost_Per_Person))+Nz([Cancel_Fee])),IIf([Cost_Category]="BOCES",Sum(Nz([Cost_Per_Person])*Nz([BOCES_Number_of_Participants])),IIf([Cost_Category]="BT BOCES",((Nz([Estimated_Youth_or_Scouts])+((Nz([Estimated_Adults-Other])-Nz([BOCES_Chaperones]))))*Nz(Cost_Per_Person)))))))))) AS [Estimated Cost], [Event Information].Cost_Category
FROM [Event Information]
GROUP BY [Event Information].Event_ID, [Event Information].Program_Code, [Event Information].BOCES_PO, [Event Information].BOCES_Number_of_Participants, [Event Information].Estimated_Youth_or_Scouts, [Event Information].[Estimated_Adults-Other], [Event Information].Cost_Per_Person, [Event Information].BOCES_Chaperones, [Event Information].Canceled, [Event Information].Cancel_Fee, [Event Information].Cost_Category, [Event Information].Cost_Category
HAVING ((([Event Information].Program_Code)="GT"));
The problem, and my question, comes in towards the end of all those IIf statesments in this section:
Code:
IIf([Cost_Category]="BT BOCES",((Nz([Estimated_Youth_or_Scouts])+((Nz([Estimated_Adults-Other])-Nz([BOCES_Chaperones]))))*Nz(Cost_Per_Person)))
I need to find a way for the query to automatically set the Estimated_Adults - BOCES_Chaperones to 0 if it is a negative number (in other words, if they can have up to 5 chaperones free of charge and they bring only 4, I want that part of the calculation to spit out 0 instead of -1).
I have found threads on how to do this, but the solutions are to default the results of an entire calculation to 0 if it's a negative, not default a section of the calculation. Such as this: https://access-programmers.co.uk/forums/showthread.php?t=132640
Before I start banging my head against a wall, trying to make things fit, I was wondering if anyone had any further suggestions.
Thanks.