Re: Who yields - client or developer? Your opinion

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

An alternative would be to put the units in a separate table and enumerate them (say 1-6) with a PK. Then in the query something like: iif( [UnitID] in (1,3) , (A*R*T), iif( [UnitID] in (2,4) , (A*R), iif( [UnitID] in (5,6) , (A/R), 0) ) )

But the first way looks nicer and is easier to understand if anyone else looks at it later.
(Guess I could default Null Thicknesses to 1mm and cut the calcs down to 2).

Graham

"Tom Ellison" <tellison_at_jcdoyle.com> wrote in message news:3CF55B4C.889B3373_at_jcdoyle.com...
> Dear Graham:
>
> While you cannot reference a column name from the query inside the Eval() string, you
can
> work around it for simple cases, which is all I expect you need.
>
> For example, in a query, put:
>
> Eval(CStr([Ingredient Rate]) & "* 7")
>
> and you will get the ingredient rate times 7 as a numeric result.
>
> The string "* 7" could come from another column in another table, where you look up the
> conversion using the units of measure.
>
> Before going too far, make up a list of all the necessary conversions. If you'll post
them
> here, I'll bet we can solve this in a flash.
>
> Tom Ellison
> Microsoft Access MVP
>
>
> Graham Bellamy wrote:
>
> > It's not that it doesn't work in a form (though it would be nice if it accepted the Me
> > keyword, or even better, simply the control name, but that's another thing). It's that
I
> > really need to use it (or an equivalent method) in a query. The reason is that this
> > Ingredient Rate is used to calculate the quantity, and from that the cost of the
> > ingredient. Then I need to calculate the total cost of all ingredients for the item,
then
> > the total cost of all items in the job. Then I would be able to create various types
of
> > cost reports.
> >
> > "Tom Ellison" <tellison_at_jcdoyle.com> wrote in message
> > news:3CF475A1.F6F26656_at_jcdoyle.com...
> > > Dear Graham:
> > >
> > > I was not thinking of putting the Eval() not only in a query, but in a formula on
your
> > form
> > > that calculates the quantity extension.
> > >
> > > What isn't working about it?
> > >
> > > Tom Ellison
> > > Microsoft Access MVP
>
Received on Thu May 30 2002 - 12:21:02 CDT