Change Negative Number to 0 in a Calculation Query (1 Viewer)

Reese

Registered User.
Local time
Today, 10:19
Joined
Jan 13, 2013
Messages
387
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:

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.
 

plog

Banishment Pending
Local time
Today, 09:19
Joined
May 11, 2011
Messages
11,611
))))))))))


What is the above and why did I quote it?

It's the last 10 characters of your equation--nothing but closing parenthesis. No human with finite patience should have to try and decipher something that nested. If you need to nest more than 2 Iif statements its time to move all the logic to a custom function in a module.


So, cut all that code out of your query and move it to a function. You pass the function all the data it needs, it does the logic and returns the desired result. Inside that function you will be able to easily test for that new piece of logic you need to add and then add the result it needs to be in that instance.
 

bastanu

AWF VIP
Local time
Today, 07:19
Joined
Apr 13, 2010
Messages
1,401
Can you try this

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",IIF(((Nz([Estimated_Youth_or_Scouts])+((Nz([Estimated_Adults-Other])-Nz([BOCES_Chaperones]))))<0,0,((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"));
 

Reese

Registered User.
Local time
Today, 10:19
Joined
Jan 13, 2013
Messages
387
Plog--

No human with finite patience should have to try and decipher something that nested. If you need to nest more than 2 Iif statements its time to move all the logic to a custom function in a module.

You're absolutely right. And it is incredibly frustrating. I'm sure modules and functions would be better but I know nothing about them other than a few that I managed to write with a lot of copy/pasting from other's work and guess work. I have no idea how to do one to calculate things.

And, to be honest, I don't have time to learn. We don't have an IT department and I'm an environmental educator who happens to be a geek. I need to make these changes and only take about 4 hours total to do it so that I can move on to my actual job.

If you can tell me exactly how these things work and help me write the code, I'd be more than happy to try that route.

Bastanu, I see where you're going with that and I am trying it but it keeps coming up with errors about syntax and/or extra ), which I think goes back to Plog's point about complications with nesting. I'll try to play around with it some.
 

Reese

Registered User.
Local time
Today, 10:19
Joined
Jan 13, 2013
Messages
387
Also, Plog, if I were to use modules and functions, would they be able to be sources for fields in subforms and subreports?

The calculated cost needs to be displayed in a subform that's tied to the form that all of these fields are visible in and data being entered in by the user. Then they need to be in a subreport that is tied to a report that is used to print out a physical Invoice for our Ticket Booth. And then the calculated cost also needs to be tied into a larger set of queries to generate reports on our income, etc.

Would a function in a module be able to replace the current query that is currently tied into all of these other forms/subforms/reports/subreports/union queries/crosstab queries, etc.?
 

plog

Banishment Pending
Local time
Today, 09:19
Joined
May 11, 2011
Messages
11,611
For [Estimated Cost] in the query, you replace that block of code with this:

EstimatedCost: get_EstimatedCost([Cost_Category], [Estimated_Youth_or_Scouts], ...(every unique field in block of code...)


Then you create a module (Ribbon->CREATE->Module) and put this code in it:

Code:
Public Function get_EstimateCost(CostCategory, EstimatedYouthScout, ...(every unique field in block of code here, but with better names)...) 
    ' takes all data necessary to calculate EstimateCost and returns that value

  ret = 0      ' return value, by default it is 0

  

  ' here is where you put the logic in the code
  ' instead of nested IIf statements, just put each one on its own line
  ' the very last line can be your new logic you want to introduce


  get_EstimateCost = ret
  ' returns value that was calculated for Estimate Cost

  End Function

Lines beginning with a single quote (') are comments and not actual code
 

Reese

Registered User.
Local time
Today, 10:19
Joined
Jan 13, 2013
Messages
387
Public Function get_EstimateCost(CostCategory, EstimatedYouthScout, ...(every unique field in block of code here, but with better names)...)
' takes all data necessary to calculate EstimateCost and returns that value

When you say "every unique field in block of code here, but with better names" which of the following do you mean?

- That you just put place holders in and I should use the names of the actual fields in the tables?

- That you are renaming my fields to something better for purposes of this module and these names can be different than the names of fields that are being referred to in the tables?

- That you are renaming my fields to be something better and that the names should be changed BOTH in this module and in my tables?

- Or something else entirely?


I was going to copy/paste and set the names listed in that top line of code to be the same as the names of my fields, but for some reason Access doesn't like "Estimated_Adults-Other" in a module and keeps highlighting that line red so I can't even experiment with it.


' here is where you put the logic in the code
' instead of nested IIf statements, just put each one on its own line
' the very last line can be your new logic you want to introduce

What "logic" are you referring to? Is the logic code the same as the query?

Does this:

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

Turn into this?

Code:
[Cost_Category]="Full Price",(((Nz([Estimated_Youth_or_Scouts])+Nz([Estimated_Adults-Other]))*Nz(Cost_Per_Person))+Nz([Cancel_Fee]))
([Cost_Category]="Discount",(((Nz([Estimated_Youth_or_Scouts])+Nz([Estimated_Adults-Other]))*Nz(Cost_Per_Person))+Nz([Cancel_Fee]))

Or do modules have different rules, etc. to follow? And if the above code does work, do I need to put anything in front of each line?

Would the last line then include an IIf statement to allow for the if/then nature of my objective?
 

plog

Banishment Pending
Local time
Today, 09:19
Joined
May 11, 2011
Messages
11,611
This one:
- That you are renaming my fields to something better for purposes of this module and these names can be different than the names of fields that are being referred to in the tables?


Your code is too nested for me to want to parse it in my brain, so let me simplify it. If the logic in the query was this (bear with me, I took a lot out and even reconfigured it):

Code:
IIf([Cost_Category]="Full Price", [Estimated_Adults-Other]*Cost_Per_Person + [Cancel_Fee], [Estimated_Youth_or_Scouts]*Cost_Per_Person+[Cancel_Fee]))

it would become this VBA code:

Code:
ret = 0

If CostCategory="Full Price" Then ret = EstimatedAdultsOther
If CostCategory="Discount" Then ret = EstimatedYouthScots

ret = ret*CostPerPerson + CancelFee

In VBA, IIf becomes If and instead of using commas to seperate the True/False cases it uses the actual word 'Then'.

Also, I simplified the logic mathematically. In the initial code there were 2 cases, both of which multiplied by the Cost and added the CancelFee. I just used algebraic simplification to move those 2 operations outside of determining which case to use.

Before you start converting this to VBA, I would give great thought to how you can simplify your logic mathematically. It looks like you repeat a bunch of operations inside each case. If that's true, move those operations outside of determining the case and do them in the end after you've assigned the value the case needs to.
 

Reese

Registered User.
Local time
Today, 10:19
Joined
Jan 13, 2013
Messages
387
Oh! I didn't even realize that you were using VBA. That's the kind of novice I am when it comes to Modules.

Though, to be honest, your most recent thing with ret just confuses the hell out of me.

And, before I continue exploring this option, please tell me this:

If this is in VBA, how can I use this as the base of other queries?

The current query is not only used in a form but also a report that generates a page to print as a physical invoice. Can VBA be used in that manner?

And I would need to apply this same technique to replace the query that is used to calculate the income after the fact, and that query is the first query in a series that is used to generate a report on income from date X and Y.
 

plog

Banishment Pending
Local time
Today, 09:19
Joined
May 11, 2011
Messages
11,611
in VBA a function returns a value. You pass it all your variables it does the logic and tells you what the Estimate Cost is. I use the variable 'ret' to hold the return value. It starts as 0 and as I test all my cases I apply the appropriate value to it and do any necessary calculations with it. When all is said and done, ret holds the return value and in the last line of code I return it to whatever called the function.

If this is in VBA, how can I use this as the base of other queries?

I am interpreting both 'this's to mean the query we are building and not the function we are building which will compose one value of that query. When you build a query you can use 2 things as datasources for that query--tables and other queries. So once we have this query built you can then use it in other queries--all of its values will be available to any queries built on top of it. Since EstimateCost will be a value in this query, any query build on top of this one will have it available.

So, if you need the EstimateCost value in a report, you would either use this query we are building as its Control Source, or you would build another query using this query as one of its data sources and base the report on that new query.
 

Reese

Registered User.
Local time
Today, 10:19
Joined
Jan 13, 2013
Messages
387
Ok, I think that I'm following you. Before trying to solve my actual problem, I'm trying to recreate my current query using your technique.

Here's the module that I have:

Code:
Public Function get_TourEstimateCost(CostCategory, EstimatedYouth, EstimatedAdults, BOCESParticipants, CostPerPerson, BOCESChaperones, CancelFee)
    ' takes all data necessary to calculate EstimateCost and returns that value

  ret = 0      ' return value, by default it is 0

  
  If CostCategory = "Full Price" Then ret = ((EstimatedYouth + EstimatedAdults) * CostPerPerson) + CancelFee
  If CostCategory = "Discount" Then ret = ((EstimatedYouth + EstimatedAdults) * CostPerPerson) + CancelFee
  If CostCategory = "Donation" Then ret = ((EstimatedYouth + EstimatedAdults) * CostPerPerson) + CancelFee
  If CostCategory = "Swap" Then ret = ((EstimatedYouth + EstimatedAdults) * CostPerPerson) + CancelFee
  If CostCategory = "TST BOCES" Then ret = (EstimatedYouth + EstimatedAdults) * CostPerPerson
  If CostCategory = "BOCES" Then ret = BOCESParticipants * CostPerPerson
  If CostCategory = "BT BOCES" Then ret = (EstimatedYouth + (EstimatedAdults - BOCESChaperones)) * CostPerPerson


  get_TourEstimateCost = ret
  ' returns value that was calculated for Estimate Cost

  End Function

And here is the query that I have:

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, EstimatedCost: get_TourEstimateCost([Cost_Category], [Estimated_Youth_or_Scouts], [Estimated_Adults-Other], [BOCES_Number_of_Participants], [Cost_Per_Person], [BOCES_Chaperones], [Cancel_Fee]), [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 is I can test this or even save it because Access doesn't like the colon in "EstimatedCost: get_EstimatedCost([Cost_Category]..."

I'm also rather confused as to how Access knows that CostCategory refers to [Cost_Category] since we haven't written any code to that effect.
 

plog

Banishment Pending
Local time
Today, 09:19
Joined
May 11, 2011
Messages
11,611
Code:
EstimatedCost: get_TourEstimateCost([Cost_Category], [Estimated_Youth_or_Scouts], [Estimated_Adults-Other], [BOCES_Number_of_Participants], [Cost_Per_Person], [BOCES_Chaperones], [Cancel_Fee])


Sorry. The above is what you would put into the Design viewer of Access. To put it directly into SQL it would look like this:

Code:
get_TourEstimateCost([Cost_Category], [Estimated_Youth_or_Scouts], [Estimated_Adults-Other], [BOCES_Number_of_Participants], [Cost_Per_Person], [BOCES_Chaperones], [Cancel_Fee])


How the function knows what values to use is defined in that first line of the function. It's called the signature line and defines the functions name and what inputs you need to supply it:

Code:
Public Function get_TourEstimateCost(CostCategory, EstimatedYouth, EstimatedAdults, BOCESParticipants, CostPerPerson, BOCESChaperones, CancelFee)

Everything between the parenthesis are variable names--yours has 7. Within the function you use those variables to reference the values you have passed from the query. So whatever value you pass to the function first will be in the CostCategory variable, whatever you pass second is in the EstimatedYouth variable, etc.
 

Reese

Registered User.
Local time
Today, 10:19
Joined
Jan 13, 2013
Messages
387
Okay, we're on the same page now! I've made the changes and it seems to work so far. I'm going to do some more testing to make sure it smoothly integrates with my other forms, reports, etc. before I fully change over but I think it should work fine.

Here's the query that I now have:
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, Format(get_TourEstimateCost([Cost_Category], [Estimated_Youth_or_Scouts], [Estimated_Adults-Other], [BOCES_Number_of_Participants], [Cost_Per_Person], [BOCES_Chaperones], [Cancel_Fee]),"Currency") 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"));

And here's the module:
Code:
Public Function get_TourEstimateCost(CostCategory, EstimatedYouth, EstimatedAdults, BOCESParticipants, CostPerPerson, BOCESChaperones, CancelFee)
    ' takes all data necessary to calculate EstimateCost and returns that value

  ret = 0      ' return value, by default it is 0

  
  If CostCategory = "Full Price" Then ret = ((Nz(EstimatedYouth) + Nz(EstimatedAdults)) * CostPerPerson) + Nz(CancelFee)
  If CostCategory = "Discount" Then ret = ((Nz(EstimatedYouth) + Nz(EstimatedAdults)) * CostPerPerson) + Nz(CancelFee)
  If CostCategory = "Donation" Then ret = ((Nz(EstimatedYouth) + Nz(EstimatedAdults)) * CostPerPerson) + Nz(CancelFee)
  If CostCategory = "Swap" Then ret = ((Nz(EstimatedYouth) + Nz(EstimatedAdults)) * CostPerPerson) + Nz(CancelFee)
  If CostCategory = "TST BOCES" Then ret = (Nz(EstimatedYouth) + Nz(EstimatedAdults)) * CostPerPerson
  If CostCategory = "BOCES" Then ret = Nz(BOCESParticipants) * CostPerPerson
  If CostCategory = "BT BOCES" And EstimatedAdults < BOCESChaperones Then ret = Nz(EstimatedYouth) * CostPerPerson
  If CostCategory = "BT BOCES" And EstimatedAdults = BOCESChaperones Then ret = Nz(EstimatedYouth) * CostPerPerson
  If CostCategory = "BT BOCES" And EstimatedAdults > BOCESChaperones Then ret = (Nz(EstimatedYouth) + (Nz(EstimatedAdults) - Nz(BOCESChaperones))) * CostPerPerson

  get_TourEstimateCost = ret
  ' returns value that was calculated for Estimate Cost

  End Function

Thank you!
 

plog

Banishment Pending
Local time
Today, 09:19
Joined
May 11, 2011
Messages
11,611
Good glad its working. From a logical standpoint you can group together multiple cases like so:

Code:
  If CostCategory = "Full Price" Then ret = ((Nz(EstimatedYouth) + Nz(EstimatedAdults)) * CostPerPerson) + Nz(CancelFee)
  If CostCategory = "Discount" Then ret = ((Nz(EstimatedYouth) + Nz(EstimatedAdults)) * CostPerPerson) + Nz(CancelFee)
  If CostCategory = "Donation" Then ret = ((Nz(EstimatedYouth) + Nz(EstimatedAdults)) * CostPerPerson) + Nz(CancelFee)

  ' the above is equivalent to the below

  If CostCategory = "Full Price" OR  CostCategory = "Discount" OR CostCategory = "Donation" Then ret = ((Nz(EstimatedYouth) + Nz(EstimatedAdults)) * CostPerPerson) + Nz(CancelFee)

 ' also you can combine > and < with =
 ' and Else allows you to define what happens if a condition isn't true
 ' which means the last 3 lines of your cases could be written on one line like so

  If CostCategory = "BT BOCES" And EstimatedAdults <= BOCESChaperones Then ret = Nz(EstimatedYouth) * CostPerPerson Else ret = (Nz(EstimatedYouth) + (Nz(EstimatedAdults) - Nz(BOCESChaperones))) * CostPerPerson
 

Users who are viewing this thread

Top Bottom