calculated field

[Guest]

I am using Access 2002. I need to calculate a ratio pertinent to each
record. What event do I use to do this? I tried this on the open event of
the form and the ratio field contains the same value for each record...I need
it calculated for each record on the form but am not sure where to put the
code. Thanks.

Here's the code:
If Urban_Rural_Code = "U" Then Vendor_Participant_Ratio =
[Participant_Count] / 175
If Urban_Rural_Code = "R" Then Vendor_Participant_Ratio =
[Participant_Count] / 75

 

[strive4peace]

put it on the form OnCurrent event, which is triggered
whenever the record changes.

I would recommend putting your code behind the form in a
private function, then you can assign it like this in the
event property

=FunctionName()

you should also assign the code to run on the AfterUpdate
event of Urban_Rural_Code

To get help on Events, press the F1 key in an event on the
property sheet

.... but better yet is to make Vendor_Participant_Ratio a
calculated field...
ControlSource --> =
IIF(nz([Urban_Rural_Code])="U",nz([Participant_Count]) /
175, IIF(nz([Urban_Rural_Code])="R",nz([Participant_Count])
/ 75,0))

NZ is null-to-zero and is used in case the control is empty


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[John Vinson]

I am using Access 2002. I need to calculate a ratio pertinent to each
record. What event do I use to do this? I tried this on the open event of
the form and the ratio field contains the same value for each record...I need
it calculated for each record on the form but am not sure where to put the
code. Thanks.

Here's the code:
If Urban_Rural_Code = "U" Then Vendor_Participant_Ratio =
[Participant_Count] / 175
If Urban_Rural_Code = "R" Then Vendor_Participant_Ratio =
[Participant_Count] / 75

Crystal's advice is one way to do it, but you can also consider
putting the calculation in a Query, and basing your form on the query:
you could put

Vendor_Participant_Ratio: [ParticipantCount] / IIF([Urban_Rural_Code]
= "U", 175, 75)

assuming that R and U are the only values that Urban_Rural_Code will
ever assume.

John W. Vinson[MVP]