Coding for Dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ALaw - 08 Jun 2007 22:13 GMT

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

Reply to this Message

KARL DEWEY - 08 Jun 2007 23:10 GMT

>>as I'm using labels on my first two pages

Use unbound text boxes and color then as labels with raised property.

>>I also don't know how to have it turn yellow when the date is three months before Now()

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

Signature

KARL DEWEY
Build a little - Test a little

> Hi,
>
[quoted text clipped - 21 lines]
>
> Cheers

Reply to this Message

Klatuu - 08 Jun 2007 23:12 GMT

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

Signature

Dave Hargis, Microsoft Access MVP

> Hi,
>
[quoted text clipped - 21 lines]
>
> Cheers

Reply to this Message

ALaw - 11 Jun 2007 17:26 GMT

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
[quoted text clipped - 36 lines]
> >
> > Cheers

Reply to this Message