Problem with function argument

[Monardo]

Hello

I'm having a problem with argument in custom function

My table is like this:

myValues
123.5
32.7
65.8
11.1

What I want to achieve is to multiply each value with the average of all values, so that at the end I get (avg(myValues) = 233.1):

myValues2
7196.963
1905.593
3834.495
646.8525

For this I would like to have a function (please bear in mind that this is simplified example of bigger problem, that is why I need function, otherwise simple SQL would suffice as mentioned below also), so that I could do something like this:

'SELECT TestFn([myValues]) as myValues2 From MyTable' or
'SELECT TestFn("[myValues]") as myValues2 From MyTable'

and I should get the table above.

My code is like this

Public Function TestFn (FieldName) as String

Dim myAvg as Double
myAvg = Davg(FieldName, "myTable")

TestFn = FieldName * myAvg

End Function

I understand the problem is that Davg needs string, but if I change to string then the multiplication does not work. The 'FieldName' parameter does not have a Type specified because that is where the problem is if I give string Avg works ,but calculation does not. If I give Number, avg does not work.

My feeling is that it should have simple solution, but...

P.S. "as String" at the end of function is not a mistake, I need number as a string

P.P.S I did not inculde NZ either as in my example it can never be Null.

 

[namliam]

Dim myAvg as double
myAvg = Davg("FieldName", "myTable")

should be the way to create teh DAvg properly, but why not do it in SQL?

Edit ow wait Fieldname is a variable, in that case assuming it is an actual column name that should work I think. However the Multiplication after it should fail.

 

[pr2-eugin]

What is the problem I can see a few. See if it helps.

1. The first line of the code, FieldName does not have a type specifier. Is it a String or Number, although it is not 100% necessary to declare the type in this case it would be best you declare what is what.

2. Again myAvg has no type declaration, again, it is good to declare what is what (again not 100% necessary)

3. DAvg would return a Null if in case the return value is not found. So try wrapping them with Nz function.

4. You pass FieldName, which until now would have been thought to be a String or as the variable name suggests, an actual field name. How can you multiply a String with a Number? Or is there something else you wanted to do? If so explain.

Considering the four above, you might need to change the code as,

Public Function TestFn(FieldName [COLOR=Red][B]As String[/B][/COLOR]) As String
    Dim myAvg [COLOR=Red][B]As Double[/B][/COLOR]
    myAvg = [COLOR=Red][B]Nz([/B][/COLOR]DAvg(FieldName, "myTable")[COLOR=Red][B], 0)[/B][/COLOR]
    TestFn = [COLOR=Red][B]100[/B] *[/COLOR] myAvg
End Function

 

[Monardo]

I agree with your comments, I just was typing stripped version of the function as in final version lot more is going on. I will edit OP with more details.

 

[BlueIshDan]

Does Nz stand for Null Zero

 

[namliam]

be carefull with the use of the word "never" but yeah...

why do this in a function?
Much more efficient if you do it in sql something like:

Select YourField * AverageYourField
from YourTable
,  ( SELECT Avg(YourField) as AverageYourField FROM YourTable) as AverageSelect

NOTE: above is complete aircode, untested and probably has typo's in it

 

[Monardo]

why do this in a function?

Well... As I mentioned somewhere in the middle of the huge text the problem is much more complicated, which would involve in sql something like:

IIF ( cdbl([Sp01 (12)])>(5*DAvg("[Sp01(12)]","t_Spoligo_Import_Temp","left([f2],2)='H2'")) AND cdbl([Sp01 (12)])>1000,1,IIF ( cdbl([Sp01 (12)])<(5*DAvg("[Sp01(12)]","t_Spoligo_Import_Temp","left([f2],2)='H2'")) AND cdbl([Sp01 (12)])<500, 0,9)) 43 times

 

[namliam]

why 43 times? because you get (12) (13) (14) etc???

Just FYI the average query can work like so as well:

Select YourField * AverageYourField
      ,OtherField * AverageOtherField
from YourTable
,  ( SELECT Avg(YourField)  as AverageYourField 
          , Avg(OtherField) as AverageOtherField 
     FROM YourTable) as AverageSelect

even removing the DAvg from that IIF makes it a lot 'more readable'

IIF (       cdbl([Sp01 (12)])>(5*AverageField1) AND cdbl([Sp01 (12)])>1000,1
     ,IIF ( cdbl([Sp01 (12)])<(5*AverageField1) AND cdbl([Sp01 (12)])<500 ,0
                                                                          ,9))

Sounds like you have a set of 'codes' like H2 that each have their limits (like >1000) and perhaps the 5*

Could perhaps be done a lot easier using a lookup table perhaps
I.e. something like
H2 0000 0499 1
H2 0500 0999 0
H2 0999 9999 9

That you can "simply" join to your table and have it return 1,0 or 9
instead of doing 1 million IIF statements

 

[BlueIshDan]

hahaha what are you working one! Holy moly