Coding for Dates

[Guest]

Hi,

I'm working on a database right now that deals with certification on
equipment.
Some of the equipment needs to be recertified every 2 or 5 years.
I have a main form "Trucks" which has buttons for all the truck numbers
A second form "Equipment" with buttons for the types of equipment
Third form displays the record for the equipment/truck selected.

Right now I have conditional formatting on the text box in the third form.
When the retirement date passes Now() the box turns red.
What I need it to do is turn yellow when the retirement date is within three
months of now (this will be different for every record)
I also need the buttons on the truck page and equipment page to turn
yellow/red depending on the truck/equipment the expired date applies to.

Is it possible for me to do this? Conditional formatting will not work for
this, as I'm using labels on my first two pages. I also don't know how to
have it turn yellow when the date is three months before Now()

Thanks a lot,

Hopefully this makes sense a little

Cheers

 

[Guest]

as I'm using labels on my first two pages
Use unbound text boxes and color then as labels with raised property.

Now() -90
OR
Date() - 90
OR
DateAdd("m",-3, Date())

 

[Guest]

Two things. In this case, you should not be using Now(). Now() contains a
time component and based on how you compare can be a day off. If your
current field is populated using Now(), you need to run an update query
against the table updating the retirement date field to remove the time
component. That can be done with DateValue([RetirementDate]) as the Update
To in the query builder.

Also modify the code that put Now in and change it to Date.

Now to find out if it is within the next 3 months:

If DateAdd("m",3, Date) >= DateAdd("m", -3, Me.txtRetireDate) Then
MsgBox " The date is within 3 mo"
End If

 

[Guest]

Thanks a lot to both of you for the help.
I got the conditional formatting part working great.

I'm still having some problems with getting the buttons on the first two
forms to turn yellow or red though.

I'd like to write a code such as
If "retirement date on the Cable form" if = to Date() Then "button on Truck
form and Equipment form will turn red"

If there any way that I can write something like that.
I'm not very familiar with coding

Thanks a lot!

Two things. In this case, you should not be using Now(). Now() contains a
time component and based on how you compare can be a day off. If your
current field is populated using Now(), you need to run an update query
against the table updating the retirement date field to remove the time
component. That can be done with DateValue([RetirementDate]) as the Update
To in the query builder.

Also modify the code that put Now in and change it to Date.

Now to find out if it is within the next 3 months:

If DateAdd("m",3, Date) >= DateAdd("m", -3, Me.txtRetireDate) Then
MsgBox " The date is within 3 mo"
End If
--
Dave Hargis, Microsoft Access MVP

Hi,

I'm working on a database right now that deals with certification on
equipment.
Some of the equipment needs to be recertified every 2 or 5 years.
I have a main form "Trucks" which has buttons for all the truck numbers
A second form "Equipment" with buttons for the types of equipment
Third form displays the record for the equipment/truck selected.

Right now I have conditional formatting on the text box in the third form.
When the retirement date passes Now() the box turns red.
What I need it to do is turn yellow when the retirement date is within three
months of now (this will be different for every record)
I also need the buttons on the truck page and equipment page to turn
yellow/red depending on the truck/equipment the expired date applies to.

Is it possible for me to do this? Conditional formatting will not work for
this, as I'm using labels on my first two pages. I also don't know how to
have it turn yellow when the date is three months before Now()

Thanks a lot,

Hopefully this makes sense a little

Cheers