Doesn't work when value column is a calculated field

I had this question after viewing Median, Mode, Skewness, and Kurtosis in MS Access.

Awesome macro!

I need to find the median of a calculated field in my table. I get an error when using the calculated field and confirmed in your own file that when using a calculated field as the value it will always return an error? Do you know if there is any easy fix for this? I tried looking at your code but I'm not sure where to start.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try creating a Query from your table, and then using that Query when calculating your Median. This should force Access to "precalculate" the calculated field before you perform other calcs.

Also - it's generally a bad idea to store calculated data anyway, so I'd suggest that you look for ways to NOT store this data in a table.
mwc360Author Commented:
I tried this and it still doesn't work. It only work works when the original table value is not a calculated column. Even when I do a query of a query and have the first query be the source data it doesn't work.

Thanks for the suggestion though.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could always use a Temporary table to do this, and "hard code" the value into that temp table. It's fairly straight forward:

Currentdb.Execute "SELECT * FROM YourTable INTO YourTemporaryTable"

This would create a table named "YourTempoaryTable" in the database, and you could then run the Meidan code against that table.

Note that SELECT INTO only works if the table does not exist, so if you need to automate this, you'd have to run this line BEFORE you run the one above:

'/ it will error out if the table does not exist, but we don't care
On Error Resume Next
Currentdb.Execute "DROP TABLE YourTemporaryTable"
'/ resume your error handling, if applied:
ON Error GoTo YourErrorHandler
'/ now create the table:
Currentdb.Execute "SELECT * FROM YourTable INTO YourTemporaryTable"
mwc360Author Commented:
There were not any good solutions. I determined that it was impossible to run the code with a calculated value.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.