yes/no fields

[Guest]

i have a text field labeled "inactive" set for yes/no results, another field
labeled "last appt. date".

is it possible to have the inactive field look at the last appt. date and if
its older than six months automatically make the inactive field yes?

 

[Dirk Goldgar]

i have a text field labeled "inactive" set for yes/no results,
another field labeled "last appt. date".

is it possible to have the inactive field look at the last appt. date
and if its older than six months automatically make the inactive
field yes?

If the *definition* of "inactive", as far as this app is concerned, is
that [last appt date] is more than six months ago, then you don't need,
and shouldn't have, a field in the table to represent inactive status.
Instead, this should be a calculated field defined by a query, or a
calculated control on a form or report. You could define the field as
the result of the expression

DateDiff("m", [last appt date], Date()) > 6

On the other hand, if "inactive" status can be set in other cases than
the six-month cutoff, then you do need a field in the table for this.
If you just want to make a sweeping update to set that field to True for
all records older than six months, you might run an update query similar
to this:

UPDATE YourTableName
SET [Inactive] = True
WHERE DateDiff("m", [last appt date], Date()) > 6

You'd have to substitute your own table and field names, of course.
Note also that the DateDiff function counts month *boundaries* when you
use the "m" interval. That means that, on 3/1/06, the date 2/28/06 is
considered to be 1 month ago. If you need finer control than that, you
need a more complex expression.

 

[fredg]

i have a text field labeled "inactive" set for yes/no results, another field
labeled "last appt. date".

is it possible to have the inactive field look at the last appt. date and if
its older than six months automatically make the inactive field yes?

On a form? Sure.
Code the Form's Current event:
[Inactive] =DateAdd("m",6,[LastApptDate]) > Date

Place the same code in the [LastApptDate] AfterUpdate event.

 

[TC]

Uh, let's hope that [Inactive] is not a bound field!

Otherwise, chaos will reign when he is only querying (not adding or
editing), and tries to move from record to record!

My bet is, he wants the change to happen automagically, to the actual
table data. In which case, it would be better off done in a database
startup module, before the forms are displayed at all.

HTH,
TC (MVP Access)
http://tc2.atspace.com