call fuction from query code (1 Viewer)

[24sharon]

i have a sample function

Function GetPercent(n As Integer) As Integer
Select Case n


Case 1: GetPercent = 100
Case 2: GetPercent = 50
Case 3: GetPercent = 0
End Select
End Function

I have a table like this

a | b | c | d | e | f | percent
----------------------------------------
1 | 2 | 1 | 3 | 3 | 1
3 | 1 | 3 | 2 | 1 | 3
1 | 2 | 1 | 1 | 2 | 1
2 | 2 | 3 | 3 | 1 | 2
1 | 1 | 1 | 2 | 2 | 3

1 = 100
2 = 50
3 = 0

for example the first row

1 | 2 | 1 | 3 | 3 | 1
(100+50+100+0+0+100) / 6

I think if I can call function from query than I write like it:

strSQL = "update table set percent = " & GetPercent(a) + GetPercent(b)
CurrentDb.Execute (strSQL)


but sure that I got an error because its not find the a and b

how can I do it?
good day!

 

[Uncle Gizmo]

At first I could see nothing wrong with your idea, or your approach. So I knocked up a quick sample database myself and tried it. And I also have an error with the query. So, then thinking that maybe I misunderstood the construction of the query, I did a simple test in a form controlled by a command button. Same thing it wouldn't work? I was puzzled? Strange error message, didn't make sense.

However I have discovered that there is a function within MS Access called "getpercent". and I believe this is the root of your problem. If you go back through your own example and add an "f" to the beginning of the function name getpercent, like this:

fGetPercent

I think you will find that it will work as advertised.

This type of error happens infrequently, but when it does it is next to impossible to identify. I have taken to prefixing all of my function names with an "f" as I have been caught like this before more than once. The typical catch for a beginner, is the "date" catch, where you use the word date somewhere in your code and cause all sorts of problems.

So like I said, by adding the "f" at the beginning of all of your functions, you eliminate the possibility of this error, now and in the future.

In the future there is the possibility that if you code a decent function, for example imagine you had written a function called "replace" in MS Access 97 where such a function did not exist. And you copy your code into a later version of MS Access where they provide the replace function, then you would have a similar error.