Re: Who yields - client or developer? Your opinion

Graham Bellamy wrote:

>> 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.
>
>Ok, that sounds like it could be a solution. Thanks for the tip. I am developing on Ac97,
>however the final product will be run on Ac2000, so I'll have to make my own function. I
>can set to doing this, but to save reinventing the wheel, if anyone out there has such a
>function already written, and is willing to share, could they point me in the right
>direction. If it's possible to do this, it would be great to have on which accepts
>multiple 'Find/ReplaceWith' pairs, so as to not need to repeat the function multiple
>times.

Here's my A97 variant of Replace:

Function Subst(Original As Variant, Search As String, _

                            Replace As String) As Variant

        pos = InStr(Subst, Search)
        Do Until pos = 0
            Subst = Left(Subst, pos - 1) & Replace _
                            & Mid$(Subst, pos + Len(Search))
            pos = InStr(pos + Len(Replace), Subst, Search)
        Loop

	strExp = Subst(Original, "[Area]", Nz(A, ""))
	strExp = Subst(strExp, "[Rate]", Nz(R, ""))
	strExp = Subst(strExp, "[Thickness]", Nz(T, ""))
	CalcQR= Eval(strExp)

And then the query field would be:

QtyRate: CalcQR(QtyFormula, [Area], [Rate], [Thickness])

--
Marsh