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?

 

[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.

 

[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.

 

[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.

 

[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?