form: displaying value based on criteria

  • Thread starter Thread starter JackieV
  • Start date Start date
J

JackieV

I cannot figure out (I spent hours agonizing over this) how to
display/calculated field on a form based on criteria.

I have a table field, 'rotWeeks', that I would like to display only if
another field value, 'rotCancel', is False.

In addtion, I would like to Sum 'rotWeeks' based on the same criteria.
For example, if there were 3 records with 'rotWeeks' values of 4, 2,
and 4 and the first one had a 'rotCancel' value of 'True' then the Sum
of 'rotWeeks' should be 6.

here is the basic table structure:

rotID <pk>
msID <fk>
rotWeeks - numerical
rotCancel - T/F

The sum of 'rotWeeks' would be by 'msID'

Any help would be great!

Thanks in advance!
JV
 
Jackie,

I hope that I'm just not reading your post correctly. Are you saying that
msID is equal to the sum of 'rotWeeks'? If so, I would strongly suggest that
you stop right now, get yourself some books, and do some reading about
database design before you expend too much of your time on this.

Otherwise, if I have indeed misinterpreted your post, I extend my most
humble apologies.

Brian
 
Hey Brian,

lol...no. I forgot to indicate 'group by'. Database design is not my
problem but forms!!

I can write a sql statement to get my results but I am trying to get it
to display on a form.

thanks
Jackie
 
Jackie,

Happy to know that I'm still having troubles with the english language!

I imagine that to display the sum, you'd probably have to resort to using
the DSum() function either in the main form's recordsource or as the control
source for an unbound text control.

As for displaying 'rotWeeks' based on 'rotCancel', the coding is pretty
straight forward:
Using both the form's On Current and rotCancel's AfterUpdate events:

Me.rotWeeks.Visible = Not Me.rotCancel

Hope that is what you're after,

Brian
 
Back
Top