Conditional SUM as a Control Source

[davidbodhi]

I'm creating an order form for a clinic.

Health-related services are not charged sales tax, but products are.

In order to have one subform to do the job (rather than one for non-taxable and one for taxable items), I'm trying to create separate running sums of taxable and not taxable items from the same query, but am not getting far. "WHERE" statements don't seem to be working in Control Source code.

Currently, there's a cboItem_Name that populates txtItem_Cost. There's txtQuantity and txtExtended_Price. All working fine. The cbo query includes whether the item is taxable or not, from a YES/NO field in the table.

I'm trying to get txtTaxable_Sub_Total to SUM[Item_Cost]*[Quantity] where [Taxable] = YES, but it ain't happenin'.......

Is there a better way to go about this, or can someone suggest correct syntax to me?

 

[Pat Hartman]

Taxable:
=IIf(Taxable = True, SUM(Nz([Item_Cost],0)*[Quantity]), 0)
non-Taxable:
=IIf(Taxable = True, 0, SUM(Nz([Item_Cost],0)*[Quantity]))

 

[davidbodhi]

Thanks, Pat.

This is very similar code to what I'd tried.

Access put square brackets around "Taxable". Is this expected? It's still not clear to me when they should be there and when not.

In any case, the code doesn't work, as is. All I get is zero in that control. Tweaking it in various ways either still gets me zero or a blank box, so I'm sure it's the syntax.

The selected item comes from a combo box. Can you tell me how I can more specifically point to the combo selection? When I navigate to the table:field in the control's Expression Builder and try using the syntax offered, I get the #Name error. Trying to point to the cbo's query column where Taxable is doesn't work, either, or my syntax for that is wrong.

Thanks for further clarifications.

 

[Pat Hartman]

Access put square brackets around "Taxable". Is this expected? It's still not clear to me when they should be there and when not.

It does that sometimes. The square brackets are REQUIRED when the name contains one or more characters that are not allowed by VBA. The ONLY characters allowed by VBA are the upper and lower case letters, the numbers 0 through 9 and the underscore. Anything else is illegal and that means spaces, dashes, pound signs, and other special characters. If your object names or field names contain any of these, you would be well advised to remove them due the the potential for problems with VBA.

All I get is zero in that control

Does the quantity field have a valid value? You may need to use the Nz() function for it also. Make sure that the control names of each of the calculated controls is not the same as that of any field in the form's RecordSource.

 

[davidbodhi]

Thanks for the info, Pat. Knowing more about brackets helps.

I've "gotten it" with regard to field and control names. None of them are the same. Fields are named, controls start with txt or cbo or lbl, or what have you, so none of them are the same.

The quantity value ought to be valid. It's set to default to 1 and is doing so.

I'm pretty sure it's the reference to the Taxable Yes/No that's at issue.

I created a text field that calculates tax for each line item, just multiplying the extended price by the tax rate, and I get a valid number for each one. As soon as I put the IIf([Taxable]=True or =Yes or =1, I get zeros.

Is there some other way I ought to be going about this? Here's the deal:
This clinic sells services and products. So, I've put all these things into one table. The services, however, are not taxed, while the products are. So, in my table I have a yes/no field for whether the item is taxable. What I'm trying to do is calculate the total tax. Now, since tax is a calculated number, I don't want to store it, right? Yet the SUM function, apparently, only works on fields, not controls. Yet, in my control where I'm calculating tax, whether as a SUM or even just within one record, an IIf isn't working. I have more complicated IIf's as Control Sources elsewhere that do work, so I think I understand how they work. Either the syntax is still wrong or I can't refer to that Taxable yes/no field in this area. Should I be going about this in an entirely different way? Should I prebuild a query and use that instead of coding right in the Control Source? Do you have some other suggestion?

 

[davidbodhi]

Actually, I have gotten a sales tax control working within individual records. By creating a control bound to the Taxable field, and having the IIf statement look at that control for "True", I am able to calculate tax where I need it. Trying to look at the Taxable field in the table just wasn't happenin'.

Now I need to get a running sum, but still not store the tax amount. So far, that is not working.

 

[Pat Hartman]

I would add a footer to the subform and add controls that sum the data. There are posts here that provide code for actually doing running sums. I find that if the recordset is too large, the code/query is excessively slow.

 

[davidbodhi]

I have the controls for doing the sums in the footer, already, Pat. Working with ScottGem, I found that they don't seem to work on calculated controls, just on values stored in fields in tables. That's how I ended up trying to get code for this working in the Control Source in the first place.

Even the Access Help file says: "The Sum function totals the values in a field."
It doesn't mention totalling values in controls. SUMming non-stored values or doing calculations on the stored values using an IIF statement are where I'm having a roadblock.

I do have successful running SUMs working in the subform footer now. When I use the same code, altered to either look for Taxable=True or looking at calculated, non-stored values, it doesn't work.

 

[Pat Hartman]

You cannot sum controls because Access doesn't have a place to store the values for calculated controls. If you want to sum a calculation, you must repeat the calculation within the sum().
Try this:
Taxable:
=Sum(IIf(Taxable = True, Nz([Item_Cost],0)*[Quantity], 0))
non-Taxable:
=Sum(IIf(Taxable = True, 0, Nz([Item_Cost],0)*[Quantity]))

The Sum() may have to be the outer function to work.

 

[davidbodhi]

Thank you, Pat. Sorry about the dual post.

That syntax works. So, my previous #error was because I hadn't included the nested Nz function, it looks like.

The Nz, in this case, prevents a Null, where no item has yet been selected, from messing up the calculation, correct? It forces any Null to be a zero added to the SUMmed [Item_Cost]?

We can get away with not using Nz for [Quantity] because it's default is 1?

And the IIf says if Taxable=True then generate a number, but if not use zero in the SUM?

Am I parsing that right?

 

[Pat Hartman]

That is correct. The IIf() has three parts - condition, true path, false path. There is no nesting required in your case but if necessary either or both the true path and the false path can contain another IIf().

I included the Nz() only for the cost because I thought it possible that the cost might be null. To be safe, you could use the Nz() for quantity also. Although aggregate functions such as Sum() handle null values properly, once you start building expressions, you need to take care of any possible nulls yourself.