Combo column: SUM, IIf, Yes/No

[davidbodhi]

I have a combo box with the following 4 columns:

ItemID, Item_Name, Item_Cost and Taxable.
The fields are Autonumbered, Text, Currency and Yes/No, respectively.

I am trying to SUM sales tax, in the form footer, based on the Yes/No settings. Some items are taxed and some are not.

So far, I am able to get tax calculations on everything, only.

With this code as Control Source for the footer control:
=Sum(IIf([Taxable]=0,([Item_Cost]*[Quantity])*0.0556,0))
I get tax calculated on every item, regardless of its taxable flag.
When I use:
=Sum(IIf([Taxable]=1,([Item_Cost]*[Quantity])*0.0556,0))
I get no tax calculated at all. Always comes out zero.

For some reason, the calculation isn't distinguishing the Yes/No values for each record, independently.

Can anyone assist me in fixing this?

 

[GumbyD]

I think I would try the following: In the Detail section of the form I would create a textbox with the formula: IIf([Taxable]=-1,([Item_Cost]*[Quantity])*0.0556,0) (note the change to -1 from 1) and call it say TaxableAmount. Then in the form footer put =sum([TaxableAmount]).

 

[davidbodhi]

Thanks for the suggestion, Gumby - I will explore the "-1" option... but SUM doesn't work on controls, only on fields in a table and, since storing calculated numbers is bad normalization, I don't think your idea will work for me.

But is -1 how Access values a Yes or a No?

It's not 1,0?

 

[davidbodhi]

Looking closer, I'm sure that the value of 'Taxable' is not being passed from my Items table to my Purchases table, so the code just can't tell what the value should be.

I have a relationship set up between the fields named 'Taxable' in each table, but it's "indeterminate" and if I try to 'enforce referential integrity' I get a message that there is "No unique index found for the referenced field of the primary table."

Yet, the name and price of the item selected in my combo is found just fine.

Any suggestions for that lil problem?

 

[GumbyD]

-1 is a yes in an Access Database and 0 is a no. As for your other problem, Access will not let you establish referential integrity if you are joining part of a multi-part key to a single primary key. I am not really sure why Taxable would be part of any of your keys anyway, but it may need to be because of the design you have in place.

 

[davidbodhi]

Pat - I know that I don't know about keys very well, but both tables have primary keys, both of which are Autonumber fields.

List of available products and services, some taxable, some not:
tblPurchase_Items
Primary Key = ItemID

List of things actually purchased in each transaction:
tblPurchased_Items
Primary Key = Transaction_ItemID

Both tables have a field 'Taxable' and both are actually Yes/No fields, not text fields.

There is a relationship already with the ItemID primary key, from tblPurchase_Items to tblPurchased_Items, fields named the same in both tables: one-to-many.

That's why I'm puzzled as to why it isn't finding an index, when I create a relationship between 'Taxable' fields and click "Enforce referential integrity".

Is something corrupt in the database? I have run "Compact and Repair". Didn't do any good, of course.

What can I have missed? By the way, neither table has a multipart key.

 

[davidbodhi]

Pat - Why does anyone do something that makes no sense or is wrong. Out of ignorance, rather than some perverse sense of deliberately hamstringing themselves.

I'd realized that the Taxable field was not coming across into my tblPurchased_Items from tblPurchase_Items. I didn't know why and was trying to make it do so. And doing it wrong.

Nevertheless, having the Primary Key from tblPurchase_Items properly linked to a same-named field in tblPurchased_Items is not bringing the 'yes' or 'no' value of the Taxable field into the tblPurchased_Items. I don't understand why. I need it to do so in order to distinguish between items and calculate sales tax.

Apart from "bringing" that value into another table, using the following code doesn't distinguish them, either.
=IIf([Taxable]=-1,(Nz([Item_Cost],0)*[Quantity])*0.556,0)

Seems to me (and I basically got this code from you) this ought to look at the Taxable field, see if Taxable is yes or no and then calculate accordingly. It doesn't.

What I *have* been able to do, is get the calculation to be global, so it calculates without regard to the value of Taxable.

Are you able to suggest why that might be the case?

 

[davidbodhi]

Thank you, Pat.

Sorry if my explanation of my situation had been less than clear. When trying multiple different unsuccessful routes, I tend to lose sight of things.

I've had the cost coming up ok, and using the code for Taxable also works (both your last suggestion and a couple of others I've tried), but, unlike the cost, which always comes up for the individual record, the Taxable value for the last item selected always applies to every record.

It's driving me crazy.

To reiterate, I've been able to get a 'response' from my Yes/No Taxable field, using the code you just suggested does work for that, but when I *do* get a response it's always applying to every record. I'm using virtually the same code for both cost and taxability - as follows:

Private Sub cboItem_Name_AfterUpdate()
Me!txtItem_Cost = Me!cboItem_Name.Column(2)
Me!txtTaxable = Me!cboItem_Name.Column(3)
Me.Refresh
End Sub

Yet, txtItem_Cost comes up for each item, individually, but txtTaxable always pops up the same in every record. I have zero clue why that is. The table where both values are coming from have differing values for each item, in a Currency field and a Yes/No field, respectively.

Can you point me in the right direction?

 

[davidbodhi]

Actually... Never mind.

It's finally sunk in, what you said about tax RATE.

Each transaction has to store the tax rate, which is not a calculated value, but imposed from outside, in order for old records not to recalculate themselves by a new tax rate.

I'd neglected to think about it being able to change, since it hasn't changed around here in so long (and is therefor up for a hike).

I'll simply dump the check box and go for a numeric field that will either have a rate or be set to zero, for those untaxed services.

I've been doing fine with numeric fields, so I am pretty sure I'm no longer stuck.

Thanks again for your assistance.