Combo column: SUM, IIf, Yes/No

D

davidbodhi

Davidbodhi
Local time
Yesterday, 18:20
Joined
Jul 6, 2005
Messages
79
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?
 
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]).
 
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?
 
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?
 
-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.
 
Start by fixing the relationship. You will need to add a primary key to the table that is missing one. A unique index will seem to solve the problem but you can't enforce RI unless you create a primary key.

Once both tables have proper keys, Access will be able to determin the cardinality of the relationship and it will say 1-1 or 1-many. Then you can enforce RI.

Your original code is fine except that you need to check for -1 which is the True value. Make sure your field really is a Yes/No field and not a text field that actually contains the string "Yes" or "No".

FYI Gumby - You CANNOT Sum() a calculation. You must REPEAT the calculation within the Sum(). So if TotalAmt is the name of a control that contains this calculation -
=fld1 + fld2
Then to sum that field, you CANNOT use
=Sum(TotalAmt)
You MUST use:
=Sum(fld1 + fld2)
 
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.
 
Last edited:
You can't just create relationships on arbitrary fields. A defined relationship is ALWAYS from a primary key in one table to the foreign key field in a second table. If the foreign key in the second table is that table's primary key or a unique index, Access interprets the relationship as 1-1. If the foreign key in the second table is not the primary key or is only one field of a multi-field primary key, Access interprets the relationship as 1-many.

In NO case would you relate a non-key field in tbl1 to a non-key field in tbl2. So, I don't understand why you are trying to relate the two taxible yes/no fields.
 
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?
 
When you add items to the purchased table, you need to copy their price and taxable status or possibly tax rate from the item table. The reason for this seeming breach in normalization is because the cost of an item and its taxible status might change over time and you always want to be able to look at the purchased table and calculate the extended cost correctly.

To do this, there are several methods. My first choice is to include the cost and taxable status fields in the RowSource query that you are using for the combo where you select the item. Don't forget to modify the column count and column widths properties after you change this query or the combo will exhibit strange behavior. Then in the AfterUpdate event of the combo, you need to add two lines of code:

Me.Cost = Me.cboItem.Column(2)
Me.Taxable = Me.cboItem.Column(3)

The columns of the combo's rowsource are a zerobased array. That means that the first field is referenced as .Column(0), the second column is .Column(1), etc. So change the column numbers in the sample code appropriately.
 
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?
 
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.
 
You must log in or register to reply here.

Similar threads

amorosik
SUM function inside textbox on the bottom of the form return #Error
Replies
8
Views
416
amorosik
amorosik
amorosik
Calculated fields not updating
Replies
4
Views
549
amorosik
amorosik
S
Using a sum of queried records in a calculation in a form
Replies
9
Views
258
SDLAW234
S
T
Cannot Sum a Calculated Control in the Report Footer
Replies
11
Views
646
The_Doc_Man
The_Doc_Man
H
Different SUM in report and query
Replies
7
Views
783
Hello1
H

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom