[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-10-08
18
Medium Priority
?
237 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 49

Expert Comment

by:Dale Fye
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 52

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 49

Assisted Solution

by:Dale Fye
Dale Fye earned 800 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 49

Expert Comment

by:Dale Fye
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
 

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 800 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

873 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