Adding Date computation and Changing the resulting Font Color

[KlausAlt]

I am creating a records database for vendors. Our industry requires
certain documentation which expires yearly, but not on Jan. 1, but
rather one year from the date of issue. I have a field where I enter
the date of issue. I have another field that I want to record the
expiration date of the first field, ideally Date=+365 of Field 5. When
I run my monthly query to find expired certificates, I would like to
have the expired ones display in red. Also, something simple. When I
enter data into a field, I would like to have some preset choices to
select from. How do I put those drop down menus in? Can it be done
with a calender? Thanks.

 

[[MVP] S.Clark]

To autopopulate the Expiration field, use the AfterUpdate event of the
startdate to autofill the Expiration date

e.g.
if not isnull(txtStartDate) then
txtExpirationDate = txtStartDate + 365 'What about leap years?
end if

Colors can be handled by the Conditional Formatting function. See help.

A combobox is the control that has a preset list of values. I think this is
what you are calling a dropdown menu. There is an ActiveX calendar control
that can be implemented as well.

 

[John Vinson]

I am creating a records database for vendors. Our industry requires
certain documentation which expires yearly, but not on Jan. 1, but
rather one year from the date of issue. I have a field where I enter
the date of issue. I have another field that I want to record the
expiration date of the first field, ideally Date=+365 of Field 5.

Don't store this field in the table AT ALL. Instead, set the Control
Source of a textbox to

=DateAdd("yyyy", 1, [DateOfIssue])

on the Form (or on any report where this date is needed).

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

When I run my monthly query to find expired certificates, I would like to
have the expired ones display in red.

Look at "Conditional Formatting" - select the textbox that you want to
have red, and choose Format... Conditional Formatting from the menu.

Also, something simple. When I
enter data into a field, I would like to have some preset choices to
select from. How do I put those drop down menus in? Can it be done
with a calender? Thanks.

It's called a Combo Box; use the combo box wizard in the toolbox.
There is also a Calendar Control that's a bit more work to insert.

John W. Vinson[MVP]