Using Function to Maintain Ref Values

[khwaja]

I have a query which basically lists stores and various works to be done in each of them which is indicated by true/false. I have about 8 things to be done to each store. Is there an option to create a function which holds the amount of money needed for each work to be done cand instead of seeing yes/no or true/false in my query I could see the cost associated with each work? Benefit is that can keep this list in one place and make changes to the amounts at will. Will appreciate an example.

 

[The John Rambo]

You can hide the True/False field by disabling Show: in the design view of the Query by clicking the checkbox. (See attached JPEG).

1. Please send me the layout of all of your tables involved (including links) e.g.
Table1: WorkToBeDone
Field1: Stores
Field2: Works
Field3: Cost

 

[khwaja]

Thanks. it is a simple one to many situation where I have a store and on the many side there a are a number of works to be done which are highlighted by true/false.
Table 1 - store
Store name
Table 2 - works
work1
work2
and so on.

I would like a function that evaluates the true/false in each field and then assigns a dollar value as the cost. So effectively replacing true/false with values. I have attched two pictures which shoes what is what it should look like.

 

[The John Rambo]

Try using the IIf Function:
MaxiCost: IIf([Maxi]=-1,160000,0)

With Currency format:
MaxiCost: Format$(IIf([Maxi]=-1,160000,0),"Currency")

 

[khwaja]

Thanks. Could I create a public function along these lines where I can keep changing the cost instead of having to amend eacg field formula.

 

[The John Rambo]

Take a look at these threads and see if they help...

Modify stored query using VBA
This one also might help

 

[khwaja]

Thanks a lot.