"Geoff" <gf****@freenetname.co.uk> wrote in message
news:n5********************@brightview.com...
Many thanks for taking the trouble to reply. Yes it was obvious when you
suggested using a extra lookup table and incorporating this into the
query - I am a bit on the slow side really
However I don't understand your comment about calling VBA code from the
query. How do you do that?
Geoff
"Anthony England" <ae******@oops.co.uk> wrote in message
news:dp**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com... "Geoff" <gf****@freenetname.co.uk> wrote in message
news:7L******************************@brightview.c om...I need to produce a report based on a query.
Cost is a calculated field and its value is dependent on another field,
in the query, called Session.
There are 5 different Session codes each generating a different Cost.
What is the simplest way to set the correct Cost, in this calculated
field, when there are so many options for a Session ?
Any help will be genuinely appreciated
Geoff
We don't know much about the tables and fields in your database - so for
all we know the solution might be quite easy or be pretty complex.
It might be that you should have a table listing which session code has
which cost - then your query could join both tables and calculate the
cost. It might be that the cost calculations are very complex and cannot
be done without calling vba functions from your query.
You could let us know further details.
There are some calculation which cannot be done with simple lookup tables.
Then you could create a new code module and write a function like this:
Silly example:
Public Function GetCost(strSessionCode As String) As Currency
Dim curCost As Currency
If strSessionCode = "CodeOne" Then
curCost = 1.42
Else
curCost = 1.94
End If
GetCost = curCost
End Function
Once the module is saved, you can call it directly from your query, eg:
SELECT MyTable.ID, MyTable.Session,
GetCost([Session]) AS Cost FROM MyTable
