Re: Who yields - client or developer? Your opinion

Graham Bellamy wrote:

>The problem with doing that is that it's not allowing the structure to change. Your
>suggestion before was to put the calculation in a field so that the formula structure can
>change for each record.
>
>Area Rate Units Thickness QtyFormula
>100 0.1 kg/Sq.m/mm 2mm [Area]*[Rate]*[Thickness]
>100 5 Sq.m/L (Null) [Area]/[Rate]
>100 0.2 L/Sq.m (Null) [Area]*[Rate]
>
>(I think the records with Null thickness implies that the thickness would in reality be
>0.2mm).
>I would then get a query to do the appropriate calculation. This would be the nice way I
>think Other possible unit combinations could be:
>L/Sq.m/mm A*R*T
>L/Sq/m A*R
>kg/Sq.m/mm A*R*T
>kg/Sq.m A*R
>Sq.m/L A/R
>Sq.m/kg A/R

I'm not sure I followed all this, but I think If you create a table with the units in one field and the expression in a second field.

   Units				QtyFormula
L/Sq.m/mm		[Area] * [Rate] * [Thickness]
L/Sq/m			[Area] * [Rate]
kg/Sq.m/mm		[Area] * [Rate] * [Thickness]
kg/Sq.m			[Area] * [Rate]
Sq.m/L			[Area] / [Rate]
Sq.m/kg			[Area] / [Rate]

then you could Join that to your table/query on the units field and calculate the QtyFormula with something like this:

QtyRate: Eval(Replace(Replace(Replace(QtyFormula, "[Area]", [Area]), "[Rate]", [Rate]), "[Thickness]", [Thickness]))

Setting up to use a Replace function is different in different version of Access. For A97 and earlier, you have to write your own, in A2K you have to write a UDF that just calls the built in Replace function and in AXP you can just use the built in Replace function.

--
Marsh