Solved

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

Posted on 2014-10-08
18
217 Views
Last Modified: 2014-10-15
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.
0
Comment
Question by:Regnillobian
  • 10
  • 3
  • 3
  • +1
18 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40369462
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40370048
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
 

Author Comment

by:Regnillobian
ID: 40371762
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40371792
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 200 total points
ID: 40371847
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
 

Author Comment

by:Regnillobian
ID: 40372045
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40372054
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
 

Author Comment

by:Regnillobian
ID: 40372084
Jeffrey, thanks for your sample. My relevant fields are all Single, same as the parameters and return value from the function.
0
 

Author Comment

by:Regnillobian
ID: 40372102
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Regnillobian
ID: 40372109
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 40372169
?
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40372175
screen
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 40372303
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
 

Author Comment

by:Regnillobian
ID: 40372575
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
 

Author Comment

by:Regnillobian
ID: 40372577
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
 

Author Comment

by:Regnillobian
ID: 40372601
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
 

Accepted Solution

by:
Regnillobian earned 0 total points
ID: 40372685
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
 

Author Closing Comment

by:Regnillobian
ID: 40381683
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now