Auto updating yes/no fields

[Guest]

Hello there;

I'm working with a table that has the following structure:

strID => person's ID
dtmStrDate => date when person started working
dtmEndDate => date when person finished work
ysnActive => Status for that person

What I would like to know is if there's a way to make the yes/no field auto
update using the following rule:

if (dtmStrDate <= today() <= dtmEndDate) then ysnActive=True else
ysnActive=False

Thanks in advance.

 

[John W. Vinson]

Hello there;

I'm working with a table that has the following structure:

strID => person's ID
dtmStrDate => date when person started working
dtmEndDate => date when person finished work
ysnActive => Status for that person

What I would like to know is if there's a way to make the yes/no field auto
update using the following rule:

if (dtmStrDate <= today() <= dtmEndDate) then ysnActive=True else
ysnActive=False

Thanks in advance.

I'd suggest that, unless the Status can sometimes be False even though today's
date is in the range and sometimes be True otherwise, that the field ysnActive
should *simply not exist*. Since its value can be derived from the date
fields, just calculate it as needed. In a Query you can use an expression:

ysnActive: (dtmStrDate < Date() AND NZ(dtmEndDate, Date()) >= Date())

This assumes that dtmEndDate might be NULL and if so you want the person to
still be considered active.

If I've misinterpreted the meaning of ysnActive post back.

John W. Vinson [MVP]

 

[Guest]

I'd suggest that, unless the Status can sometimes be False even though today's
date is in the range and sometimes be True otherwise, that the field ysnActive
should *simply not exist*. Since its value can be derived from the date
fields, just calculate it as needed. In a Query you can use an expression:

ysnActive: (dtmStrDate < Date() AND NZ(dtmEndDate, Date()) >= Date())

This assumes that dtmEndDate might be NULL and if so you want the person to
still be considered active.

If I've misinterpreted the meaning of ysnActive post back.

John W. Vinson [MVP]

Actually, your suggestion works very well so far. I have to change some
relationships and queries but overall it works as intended.

Thanks again for your help Mr. Vinson.