Date calculation

[RileyBK]

I have a database of members of a club. I have a field in my database
that says whether a record is "Active". This is determined by whether
the date in another field (membership expiration date) is more than 30
days before the current date.
I want the "Active" field to automatically update to display "Yes" or
"No" depending on the contents of the expiration date field.

Any advice?

I am utterly new to Access scripting/programming, but have some
outdated experience in other scripting/programming environments.
(That is, I'm OK on concepts, clueless on actual Access commands and
syntax.)

Thanks for any help.

 

[tina]

my advice is: get rid of the "Active" field in the table. since you're
basing the value in that field on the value in another field (expiration
date field), the Active field is a calculated value. storing calculated
values violates normalization rules. you can use the expiration date value
at any time to query for Active members, inactive (terminated? former?)
members, memberships that up for renewal, or late, or... you get the idea.

hth

 

[RileyBK]

Thanks for the advice. I think I'll take it. (And the field is not a
calculated value--calculating it was what I was asking how to do. I
was entering the "Yes" and "No" manually--ridiculous, no? But as I
said, I know zero about programming Access.)

I need to redesign generally; the "Active" field was a workaround
anyway until I get around to setting up all the queries I want in the
database.

-Riley

 

[John W. Vinson]

You can use a calculated field in the query by typing:

Active: [Membership Expiration Date] < DateAdd("d", -30, Date())

This will be True or False depending on the value in the datefield.

John W. Vinson [MVP]