I am trying to do a long expression in a query that will then be used by a report to output a printed sheet. But the formula is fairly lengthy and exceeds the (what I consider pretty crappy) limitation. How can I get around this? I'm assuming I need to use VBA to run the expression but how do I make this 'link' in to the form?

Depends what the formula is. Wanna show and tell?

pootle flump ur codings are working excelent.

Pleased to mate, I just didn't want to scare people off with the length of the formula, as it's not really complex in the least, just long.

Qty: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=1,
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+
[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+
[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+
[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+
[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+
[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+
[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+
[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+
[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+
[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QJtyPer],
IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=2,IIf([tblMainTable]![S/T?]="Trim",
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+
[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+
[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+
[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+
[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+
[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+
[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+
[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+
[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+
[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],
IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=3,IIf([tblMainTable]![S/T?]="Seats",
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+
[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+
[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+
[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+
[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+
[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+
[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+
[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+
[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+
[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],0),0))),0))

It is looking to see if the settees ordered are Complete (seats and trim parts) or seats only or trim parts only. Then it multiplies the amount of each of the 10 variations of each product by the amount ordered. This same multiplication is done 3 times on the Iif statement of which option is selected in the opgComplete option group.

In reality a very simple calculation... just very long.

This looks like the root of the problem might be a repeating groups problem
First normal form - Wikipedia, the free encyclopedia
Usually calculations like this, especially when simple sums, are due to a design flaw. SQL does calculations against rows very well, but not against columns because there is very rarely a need.

Assuming you don't want to discuss the design of your table (no one ever seems to want to) then I would recommend using four columns to do this. Put the three calculations in three columns and put the Iif in a fourth column, referencing the column that contains the correct calculation.

pootle flump ur codings are working excelent.

I have no aversion to discussing my table design but to skip that right this second, I did try what you suggest with the 4 columns and it did work perfectly, except for one very bizarre hurdle I couldn't fathom.

It asked for the first 3 columns as if they were parameters. However, it didn't actually use what I put in as a 'parameter', it did the calculation exactly correctly. UNLESS you put a zero or no answer in as the 'parameter' in which case you got no results.

So yes, your idea should be the solution in theory but I don;t know how to avoid these non existent parameter boxes appearing.

Can you post your SQL in full?

pootle flump ur codings are working excelent.

there is a finite limit for how long your SQL can be in Access JET. there is also a limit on columns in a query (IIRC its 250)
if you find you are hitting this problem do it as a series of linked queries which have a one to one join

top level query
select Q1.acolumn,Q1.bcolumn,Q1.column,Q1.dcolumn, q2.ecolumn,q2.fcolumn,q2.golumn,q2.hcolumn from Q1
join Q2 on Q2.thiscolumn=Q1.thatcolumn
sub queries
Q1: select acolumn,bcolumn,column,dcolumn from atable
Q2: select ecolumn,fcolumn,golumn,hcolumn from atable
bear in mind you can shorten the length of the SQL by using the as clause

eg
select a,b,c,d from myincrediblylongnamedtable
select t1.a,t1.b,t1.c,t1.d from myincrediblylongnamedtable as t1

Pootle Flump: to paste here the SQL I recreated it that way (because I'd been trying to find an alternative way as I was having trouble), but whenever I close the Append Query it removes the 3 columns that are doing the expressions that the Qty field adds up and appends to the tblOrders. Here is what it is:

Complete: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=1,[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],0)

Trim: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=2,IIf([tblMainTable]![S/T?]="Trim",[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],0),0)

Seats: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=3,IIf([tblMainTable]![S/T?]="Seats",[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],0),0)

Then
Qty: [Complete]+[Trim]+[Seats]

But it is now asking me for Complete, Trim and Seats as parameters, rather than using the results from the expressions above and concatenating what I put in the parameter boxes, not even adding them. ie if i put 5 in Complete parameter box, 6 in Trim and 7 in Seats it puts the results in Qty field as 567!

Where am I going wrong?

Do you know how to get in to SQL view? In 2007, leftmost icon of Results in Design ribbon. Go to SQL View then copy and paste it all here. The design view is a propriety Access thing that doesn't translate well to the forum.

pootle flump ur codings are working excelent.

I don't think you get that kind of immediate alias availability in jet-sql...

If you insist on moving forward with this design (which will continue to cause you massive headaches), you will probably have to wrap you're calculated values up in to their own query that can be called by subsequent queries wishing to use your field aliases.

Martin,

What I would suggest if this is related to a question relating to another forum, the form in 1.jpg could be treated as an entry point and then update a table with each Product against each Order. In effect you have a non-normalised entry form with normalised data because then you can do a simple sum(Order * Qty).

Simon

Ok, I'm on the brink of losing my mind (and job) on this now, because what should clearly (to me) be working, isn't. I am getting ridiculous, unexplainable problems with my queries whereby it returns different results depending on what other products are on the form, even though each query looks at different boxes in the form, the only universal one being the customer and the order number.

So... I have started again from scratch, doing my tables the way you suggest. What I don't understand though is how to set the relationships up.

I've attached a picture of how it's set up so far but I don't get how this Quantities Per Variation should work (so it is probly wrong on the pic)

- Each Product has multiple Items. (eg a settee has seats, arms, platforms etc)
- Each Product has multiple Variations. (eg a settee can be 2seater, 3 seater etc)
- Each Product will have a different set of Variations to the next. (eg one settee might have a chair, 2seater, 2½seater and 3seater, where another has a 2seater, 3seater, bed and corner unit)
- Each Item needs a Quantity in each of those Variations. (eg a 2seater has 2 seats, 2 arms, 1 platform; a 3 seater has 3 of those same seats, 2 of those same arms and 1 platform which is different to the 2seater, ie some Items are on more than one Variation, some are unique to one Variation and the quantity can differ)

So should the Products be related to these Variations or the Items?

Extra info: My old setup had 10 Variations on each Item (where it had less than 10 the extras were all 0s) and each Product stored the name of those Variations (where it had less than 10 the extras were all empty). The form updates when you choose a Product with the Variation names next to the text box for entering the quantity ordered, which it then multiplied by the quantity of each Item on that Variation.
It ran append queries for each product separately (the form displayed a possible 9 products for an order) then a select query collated all the items in the table it appended to that had the same order number.