Question Use Excel formula in Access

[glawson]

Hi,

I have a weight conversion formula in excel which looks at the values in two different cells and provides a result in a third cell.

I could really do with this 'conversion' happening in a query in Access based on values within two fields which are in the same table (I have Access 2010).

Can someone help me please?

The table is called "Dove" and the two fields that contain the data used for my conversion are called "Wt" (which is numeric) and "App" (which is text).

The formula from excel is...

=IF(A3=0,"?",IF(NOT(ISBLANK(B3)), IF(A3<112,,QUOTIENT(A3,112)),QUOTIENT( A3,112))&IF(NOT(ISBLANK(B3)),CHOOSE(MOD(QUOTIENT(A 3-QUOTIENT(A3,112)*112,28),7)+1,"","¼","½","¾")&"cwt ","-"&QUOTIENT(A3-QUOTIENT(A3,112)*112,28)&"-"&MOD(A3,28)))

... where cell A3 is the "Wt" and B3 is "App"

FYI... This formula converts a single Lbs figure to old fashioned cwts-qtrs-lbs. The "Wt" is Lbs, and the "App" is either blank if the Wt value is exact, or populated "app" if the Wt is approx.

Here's hoping!
Gareth

 

[vusi_dube]

In access where you would reference a cell in Excel, In Access you must reference the Field you'd like to do the calculation on into the field you'd like your results to appear. I hope this helps.

 

[Mihail]

Can you show me the formula for conversion in the mathematical language using fields names as variables ?

I understand that you have a function with a single variable Wt.
So, F(Wt) = mathematical formula ?????

Then the results should be show as "1/4" or "1/2" or "3/4" if F(Wt) = 0.25 or 0.5 or 0.75
or as "app 1/4" or "app 1/2" or "app 3/4" in regarding of the most appropriate value of F(Wt).

Is this what you are looking for ?

 

[glawson]

I really don't know the mathmatical formula. I simply have a spreadsheet with the calucation and formula in it. Maths and formulae are far from my strong point.

All I can do is give you some examples on the spreadsheet, that way you might be able to decipher/understand better?