Long expressions in a query -> code instead?

[torok]

Hi there everyone,

I've got records that are orders. Each order has about 10 possible items. Each item has a checkbox stating whether or not PST will be charged on the item for that particular order (it has to be done this way).

I wanted to make a query that calculated the PST for each order and put it in a separate field, to go into a report, but the expression is too long (>256 chars). Is there some way I can do this with code instead? Right now I have something like:

order_pst: iif(planks_pst_bool,planks_cost*dlookup("[tax_rate]","tbl_tax","tax_name = 'GST'),0) + iif(permits_pst_bool,permits_cost*.... etc etc.

Any suggestions??

Thanks! This forum has saved my butt many times....

 

[torok]

A better way?

I appreciate the response... I'm not yet a professional, experienced database designer but I'm learning fast, and still have another course to take on this stuff before I finish my degree. I would really appreciate any suggestions you might have on a better design for this type of thing!! =)

I'm also learning VBA: having come from a background of C and Java, I'm finding it frustrating, so thanks for enduring my newbie questions.

I'm not shy about accepting constructive criticism or suggestions....

Tom

 

[torok]

Thanks!

Pat - thanks for the great advice. I didn't really explain the problem very well, but I can use a similar solution to the one you described - Should have thought of it before! The problem is that my order has many items, and sometimes I want to charge the Provincial Sales Tax on them, but sometimes not. But you're right, instead of checkboxes for each item, specifying whether I want PST to be charged on it for that invoice or not, I should have a separate table with different cost codes, i.e.
Code | Desc | PST
001 |Planks with PST| 0.075
002 |Planks without PST| 0

etc etc. Then use a combo box. I could probably normalize it even more..... It'll take some thought

Again, I really appreciate the assistance! Thanks!