Calclulation limitation in numerical and currency expressions in Access

[Jerry Call]

It appears that one cannot combine a numeric-type with a currency-type in a single Access expression.


Suppose, for example that NumberInFullCase is numeric type (e.g., 220), while FullUnitCaseCost ($45.99) is currency.
Calling for FullUnitCaseCost/NumberInFullCase in the hope of setting a default value for a currency PieceCost field
in a table's design section crashes.

I tried making everything numeric, but that didn't work either.

It seems ridiculous that I should have to use an Excel spreadsheet to calculate the necessary values
and then copy the results into my Access table. Access reports let me do all kinds of calculations,
but I can't figure out how to get results for a "mixed" item in a table or its accompanying form.

[kd2017]

Are you trying to create a calculated field in your table? If so you probably shouldn't anyway, just use a query to run your calcs.

[ssanfu]

Calculations should NOT be done at the table level, they should be done in a query.
If you have a table with fields NumberInFullCase (as long integer) and FullUnitCaseCost (as Currency), then create a query

SELECT tblCurrencyCalc.NumberInFullCase, tblCurrencyCalc.FullUnitCaseCost, [NumberInFullCase]/[FullUnitCaseCost] AS PieceCost
FROM tblCurrencyCalc;

(change tblCurrencyCalc to the name of your table)
the result is 0.209045454545455.


With Rounding

SELECT tblCurrencyCalc.NumberInFullCase, tblCurrencyCalc.FullUnitCaseCost, Round([FullUnitCaseCost]/[NumberInFullCase],2) AS PieceCost
FROM tblCurrencyCalc;

the result is 0.21

You can format the value in a control on a form/report the way you want. (Currency)

[Jerry Call]

Yes, I see the light now. The calculations can be done in a query. I discovered they can
also be done in a report. So this one is "solved."

Alas, I am still unable to locate the tag that marks the thread as solved. If you would just put the relevant button at the top of the Quick Reply it would be much more intuitive.

[pbaldy]

Alas, I am still unable to locate the tag that marks the thread as solved. If you would just put the relevant button at the top of the Quick Reply it would be much more intuitive.

FYI you can mark the thread solved via the "Thread Tools" dropdown at the top of the thread. I doubt the forum owner can control where it is.