Calculate a field depending on what is selected in another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have an Education datasheet subform with Completion_Date, Type, Course,
and Hours.

Is it possible to have the Hours field multiplied by 16 (for a 16 week
course) if Semester in the Type column is chosen just so the user does not
have to multiply it out before entering hours?

And if Quarter in the Type column is chosen instead then multiply the
entered corresponding Hours by 4 (for a four week course)?

Else don't perform any calculations on hours?

Sorry I don't know any VB.

Thanks in advance,
David
 
Hi, David.

If you truly wish to *store* the result of the calculation in the Hours
field, you could use the AfterUpdate event of the form. Understand, however,
that this is generally not recommended because, you could create this
calculation at any time with a query or calculated form or report control,
and should the user be able to work with the table directly, without using
the form, the calculation would not take place.

Another approach would be to add another control to display the calculation
and set its Enabled property to No to prevent the user from entering the
control. If you truly have only three cases, a nested If statement would be
OK. Set the Control Source to:

=If([Type] = "Semester",[Hours]*16,If([Type]="Quarter",[Hours]*4,[Hours]))

If you want to take the first approach, use the [Hours] AfterUpdate event.
Show the Properties in Design View, click on the Event Tab, move the cursor
to AfterUpdate and click on the ellipsis to the right of the window. If you
are shown a menu, choose Code Builder. Access will then create the skeleton
of the event procedure. Here a Select Case system is more readable.

Select Case [Type]
Case "Semester"
[Hours] = [Hours]*16
Case "Quarter"
[Hours] = [Hours]*4
End Select

Note that in both cases I have assumed that the [Type] field was a text
field. I doubt, actually, that this is the case. If it is a numeric code
instead, substitute it in both cases without the quotation marks (which
delineate a string).

Hope that helps.
Sprinks
 
Thanks Sprinks that works great. I appreciate you taking the time to help.

Sincerely,
David G.

Sprinks said:
Hi, David.

If you truly wish to *store* the result of the calculation in the Hours
field, you could use the AfterUpdate event of the form. Understand, however,
that this is generally not recommended because, you could create this
calculation at any time with a query or calculated form or report control,
and should the user be able to work with the table directly, without using
the form, the calculation would not take place.

Another approach would be to add another control to display the calculation
and set its Enabled property to No to prevent the user from entering the
control. If you truly have only three cases, a nested If statement would be
OK. Set the Control Source to:

=If([Type] = "Semester",[Hours]*16,If([Type]="Quarter",[Hours]*4,[Hours]))

If you want to take the first approach, use the [Hours] AfterUpdate event.
Show the Properties in Design View, click on the Event Tab, move the cursor
to AfterUpdate and click on the ellipsis to the right of the window. If you
are shown a menu, choose Code Builder. Access will then create the skeleton
of the event procedure. Here a Select Case system is more readable.

Select Case [Type]
Case "Semester"
[Hours] = [Hours]*16
Case "Quarter"
[Hours] = [Hours]*4
End Select

Note that in both cases I have assumed that the [Type] field was a text
field. I doubt, actually, that this is the case. If it is a numeric code
instead, substitute it in both cases without the quotation marks (which
delineate a string).

Hope that helps.
Sprinks

davidg2356 said:
Hello,

I have an Education datasheet subform with Completion_Date, Type, Course,
and Hours.

Is it possible to have the Hours field multiplied by 16 (for a 16 week
course) if Semester in the Type column is chosen just so the user does not
have to multiply it out before entering hours?

And if Quarter in the Type column is chosen instead then multiply the
entered corresponding Hours by 4 (for a four week course)?

Else don't perform any calculations on hours?

Sorry I don't know any VB.

Thanks in advance,
David
Click to expand...
Click to expand...
 
Back
Top