complete beginner in writing codes

[mocamira]

Hi,
I have just started "coding" in VBA 2 days ago...
I am trying to do a function as follows:
Ratio = value/Davg(value)
value being a field in a table we can call table.
I got to this stage:
Function Ratio(value)
Ratio =
End Function

I have managed Ratio = 1/value, or Ratio = value +1 and it works, but how can I use ready done functions like DAvg in a function I'm creating???
Mocamira

 

[Robert_Mc]

Try this:-

Public Function Ratio(Value As Double) As Double
Ratio = Value / DAvg("value", "table")
End Function

put this code in a standard rather than a class module

This assumes your table name is "table" & the name of the field you wish to 'ratio' is "value".

You can use this function in any query in the same way you would use a built-in function; for example:

Expr1:Ratio(value)

Hope this helps

 

[mocamira]

That works thanks!
But is it possible to do the same thing for different tables with different fields... like this
Ratio = Value/DAvg("value1","table1")

and the same ratio to work for value 2, table 2...

Like the SUM function for example, it works with all the tables and values you want. Well I want a function that does the ratio of a value/DAvg of the whole distribution for all my tables and values (value1,2,3...n)

Is this possible or do I have to define Ratio 1 for table1,value 1 and then ratio 2 for table2, value2...!

Thanks!

 

[Mile-O]

Like the SUM function for example, it works with all the tables and values you want. Well I want a function that does the ratio of a value/DAvg of the whole distribution for all my tables and values (value1,2,3...n)

One table at a time though or ALL tables in one go?

 

[mocamira]

One table at a time though or ALL tables in one go?

I don't know... I would like to create some sort of general function... Probably one table at a time.

 

[Mile-O]

Public Function Ratio(dblValue As Double, strField As String, strTable As String ) As Double
    On Error Goto Err_Ratio
    Ratio = dblValue / DAvg(strField, strTable)
Exit_Ratio:
    Exit Function
Err_Ratio:
    Ratio = 0
    Resume Exit_Ratio
End Function

Call like this:

MyRatio: Ratio([MyValueField], "FieldName", "TableName")