How do I sort by a function-calculated value in a Microsoft Access query?

I have a calculated field in a Microsoft Access query. I duplicated this calculated field through a few different queries because I needed it there, but realized when I need to change the calculation (as I do regularly) it is so much easier and better to have the value be calculated in a function in a module. I thought this would be the perfect solution, but found that although I can sort a query on a field calculated in the query, it will not sort on the "same" value if it comes from a function. No clue why this would be. It says "Data type mismatch in query expression." when I ask it to sort. How can I pull this off? Surely there is a better way than to have to update the query every time in a dozen places.
RegnillobianAsked:
Who is Participating?

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

x
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.

Dale FyeOwner, Developing Solutions LLCCommented:
Can you post your function?  It is likely that you are returning a Variant or a String from your function, and depending on what the data looks like, it may not sort "correctly".    It would also be nice to have a sample of the record data you are passing to the function which will not sort properly.  The easiest way to do this would be to one or more of the queries, the function and some of the data from the table(s) you are using in your query into a sample database and post it here.

Make sure you remove any proprietary or personal information from the file you post here.
0
Gustav BrockCIOCommented:
You do it this way:

Select
    Field1, Field2, Field3, YourFunction([Field2], [Field3]) As CalculatedValue
From
    tblYourTable
Order By
    YourFunction([Field2], [Field3])

Of course, here YourFunction must accept whatever values [Field2] and [Field3] may hold.

/gustav
0
RegnillobianAuthor Commented:
Okay, I set up a test case so I could eliminate the proprietary information. When I first set up my test case, it worked perfectly. Then I realized my test case has all values supplied for the columns and my actual case does not, so when I left a few values unspecified, I get an #Error value and the problem shows. Here is my function.

Function TestCalc(One As Single, Two As Single, Three As Single) As Single
    TestCalc = ((Three / 5) + (Two / 3) + (One / 2)) / 12
End Function

Here is the query.

SELECT tTest.ID, tTest.One, tTest.Two, tTest.Three, TestCalc([One],[Two],[Three]) AS ResultCalc
FROM tTest;

And here is some data.

ID      One      Two      Three      ResultCalc
1      1      2      3      0.15
2      1.1      2.2      3.3      0.16
3      0      0.5      1      0.03
4      0.1      0.2      0.3      0.01
5      0.5      1      1.5      0.07
6      9      8      7      0.71
7      9.9      8.8      7.7      0.79
8            5            
9      5            5      
10      5      5            
11            5      5      
12      5                  
13                  5      

Records with ID 8 to 13 have #Error in the ResultCalc field, but it didn't show on the copy and paste.

So my question at this point is how to capture fields with no value? I had actually tried "If IsNull(One)" before to catch this and set a default value, but it didn't work. Any suggestion on how to catch record columns with no entered value and supply a default? That should fix my problem here. Thanks.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
Makes sure that all the values in your fields are of the correct data-type
If your fields are numbers, ...make sure the fields are defined strictly as a numerical datatype (so no other values are allowed)

See he attached sample...

Unsorted, the query runs fine (albeit with one #Error result, because vID 4 in col Val2 is a text string "e")
...but as son as you try to sort the query, ...you get the dreaded "Data type mismatch in query expression." error.
Database36.mdb
0
Dale FyeOwner, Developing Solutions LLCCommented:
When you have missing data (NULLs), you must account for that when your define your function, you might try:
Function TestCalc(One As Single, Two As Single, Three As Single) As Single
    TestCalc = ((NZ(Three,0) / 5) + (NZ(Two,0) / 3) + (NZ(One,0) / 2)) / 12
End Function

Open in new window

This will ensure that none of your numerators are NULL values.  Although that would not normally cause an error with this particular function, it would if you tried to divide by one of those parameters.
0
RegnillobianAuthor Commented:
I had tried NZ in the query itself before calling and expected that to work and it did not. I will try it in the function as you have it, although I wouldn't expect that to make a difference.
0
Dale FyeOwner, Developing Solutions LLCCommented:
the NZ( ) function operates differently in a query (returns a string) and in VBA (returns whatever data type you specify), so you might be surprised.

I didn't get a chance to look at Jim's sample so make sure you do.
0
RegnillobianAuthor Commented:
Jeffrey, thanks for your sample. My relevant fields are all Single, same as the parameters and return value from the function.
0
RegnillobianAuthor Commented:
This still returns #Error if any of the Single fields are not defined (null). Not sure why. I would love to be able to handle this and just use a default value if any of the fields are undefined/null.

Function TestCalc(One As Single, Two As Single, Three As Single) As Single
    Const NegOne As Single = -1!
    TestCalc = ((Nz(Three, NegOne) / 5) + (Nz(Two, NegOne) / 3) + (Nz(One, NegOne) / 2)) / 12
End Function

Open in new window

0
RegnillobianAuthor Commented:
Maybe the error happens in Access right when I pass the null parameter to the function? I don't have any way to test that, since that is an internal operation for Access (calling the function from the query).
0
Jeffrey CoachmanMIS LiasonCommented:
?
What is "NegOne" in your function there?


In any event, ...also try declaring your arguments as "Variants"
Function TestCalc(One As Variant, Two As Variant, Three As Variant) As Single
    TestCalc = ((Nz(Three, 0) / 5) + (Nz(Two, 0) / 3) + (Nz(One, 0) / 2)) / 12
End Function
...this seemed to work ok for me...

Sample attached
Database59.mdb
0
Jeffrey CoachmanMIS LiasonCommented:
screen
0
Gustav BrockCIOCommented:
Or, using the original function, correct it at the source:

SELECT
    tTest.ID, tTest.One, tTest.Two, tTest.Three, TestCalc(Nz([One],0),Nz([Two],0),Nz([Three],0)) AS ResultCalc
FROM
    tTest;

/gustav
0
RegnillobianAuthor Commented:
Gustav, I just tried this and it does work. I am not sure why I was having a problem earlier with putting the Nz right in the query. But I will opt to put the null test into the function to keep all of the work in one location, the function.
0
RegnillobianAuthor Commented:
Jeffrey, "NegOne" is declared as a Const in the first line of the two line function. It's right in the code I posted (http://www.experts-exchange.com/Database/MS_Access/Q_28534070.html#a40372102).

Thanks for mentioning the Variants as the parameter type. This is what finally allowed me to get it to work.
0
RegnillobianAuthor Commented:
I always thought Nz stood for "null or zero" and that it handled both null and zero values, converting them to something else. Glad I learned it means "null to zero" and only operates on nulls. I don't want my zero values converted to another value. Apparently, I learned this wrong many years ago. (Not sure if it was me or them.)
0
RegnillobianAuthor Commented:
Here is the final function that works perfectly. I was basically there when I first posted except that I needed to use Variant parameters instead of Single. By the time I posed, I removed the Nz in the function because it doesn't work with typed parameters. I was getting the error with or without Nz, because you can't pass a Null as a Single.

Function TestCalc(One As Variant, Two As Variant, Three As Variant) As Single
    Const NegOne As Single = -1!
    TestCalc = ((Nz(Three, NegOne) / 5) + (Nz(Two, NegOne) / 3) + (Nz(One, NegOne) / 2)) / 12
End Function

Open in new window

0

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
RegnillobianAuthor Commented:
My final post isn't necessarily the "best solution" but just putting a few of the other posts together and showing the final result in my case.
0
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.