Creating a function using IF and referencing qry field

[Gary_Mc_Mahon]

Hi,

I was wondering if anyone can help with this one....

I want to create a function that will look at a revenue period and return the revenue for that period.

E.g

If Period# = "06" then show me the reveune field "June Rev" from the query "qry_Key_Rev"

Here's as far as I've got so far...all help welcome. The reason I dotn want to do it as an IIF statement in a select query is cos i dont want to have to update the query every month.

Function FindKeyRev(Period As String) As Integer

If Period = "06" Then FindKeyRev = [qry_Key_Rev]![Jun03 Rev $]
Else
FindKeyRev = 0
End If

End Function

Cheers
Gary

 

[SydneyGeek]

Hi Gary,

you can do this with a parameter query. Each time you run it, you'll be prompted to enter the revenue period. Here's how...

Open your query in Design view.
Go to the Period field (presumably it's Text if you're using 06, but whatever...)
In the Criteria row below this field (where you want the filter to operate), type
[Enter the Revenue Period]
Save and close.

Now when you run the query, a dialog pops up. Enter the period and you're away.

Denis

 

[Gary_Mc_Mahon]

Thanks Denis, but I dont want to do it that was as it will only pull out static fields, my example should have been clearer.....

If Period = "06" Then FindKeyRev = [qry_Key_Rev]![Jun03 Rev $]
Else
If Period ="07" Then FindKeyRev = [qry_Key_Rev]![Jul03 Rev $]
Else
If Period ="08" Then FindKeyRev = [qry_Key_Rev]![Aug03 Rev $]
Else
FindKeyRev = 0
End If
End If
End If

And so on......

As you can see, if the period moves the field selected out needs to move as well.

Hope this is clearer.
Gary

 

[SydneyGeek]

Hi Gary,

do you have each month in a separate field? If you do, you'll create endless hassles for yourself. The standard database way to do it is to have 2 fields: RevenueDate and RevenueAmount. You can then filter these any way you want to pull out the relevant data -- and a crosstab query will show the revenue broken out by months.

HTH
Denis

 

[Gary_Mc_Mahon]

OK, perhaps I need to look at the solution again, its something I've been passed over.

Thanks for the help and tips.
Gary