[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register 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
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 3
  • 3
  • +1
18 Comments
 
LVL 48

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 48

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 48

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

656 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