Figuring dates

[Wulf13]

Ok, here's my quandry:

I have a form used for tracking certain "reports" within my office. One control comes from a table and its the report "Date Started". A second control calculates the "Date Expired" which is normally "date started + 180 days". Now here's the tricky part, I have three other date controls for when extensions are granted, they are ext1, ext2, ext3; each is a 30 day increment.

I'd like my form to recalculate the "date expired" as each ext date is filled in.

So when ext1 is filled in then "date expires" displays the results of "date started +180 +30" same with ext2 and ext3 except instead of 30 it would be 60 and 90.

I think I have to write VB code for this one but I don't know how to manipulate VB code within access. Normally I'd try and teach myself to do this but I'm running against a deadline and would like to get this done. Thank you.

-Wulf

 

[WayneRyan]

wulf,

All you really need to have is the initial date in your table.

You don't want to calculate a bunch of other dates and store
them also, because it will drive you crazy when data starts
changing.

Store the initial date.

In the queries for your forms/reports, you can easily add new
fields like:

ExpireDate: [YourDate]+30

ThreeMonthsAgo: DateDiff("m", -3, [YourDate])

Access will let you do a lot of calculations with dates, you
don't want to store them.

On a form, when they enter the ext date, you can put:

Me.ExtDate+30

into an unbound textbox to DISPLAY the expiration date. Don't try
to save it, you can calculate it easily later. (If you save it &
then change ExtDate; you will start to have big problems).

Wayne

 

[Wulf13]

So your saying that I shouldn't store my extension dates? I know not to store the "date expires" because it changes too much but the extension dates for each record don't change once they are given to that specific record. So why can't I store them?

All I want is an unbound textbox that when the data on the form is refreshed, it looks in DateStarted, ext1, ext2, ext3 and depending on whether the value is null or not it calculates DateExpired as DateStarted +180 or DateStarted +210, and so on. This sounds simple enough yet it sounds like your telling me its not possible?

And I'm still new to using Access but what exactly is the Me in Me.ExtDate+30

 

[WayneRyan]

Wulf,

I'm not saying that you can't do that. You only have to store what
can't be calculated.

Your definition of DateExpired:

"A second control calculates the "Date Expired" which is normally
date started + 180 days"

DateExpired shouldn't be stored in a table.

It is calculated in a query as:

DateExpired: [StartDate]+180

A control on a form can have it's Control source set to the above.

You just don't want to store it because then you open the door for
[StartDate] to change without it also being changed. That leads
to BIG problems.

I hope I'm understanding what you mean.

All I want is an unbound textbox that when the data on the form is
refreshed, it looks in DateStarted, ext1, ext2, ext3 and depending
on whether the value is null or not it calculates DateExpired as
DateStarted +180 or DateStarted +210, and so on.

Addressing that specific problem, you need a ControlSource something
like the following (I don't know what combos can be null):

=IIf([Ext1] Is Not Null And [Ext2] Is Null And [Ext3] Is Null, [StartDate]+180,
     IIf([Ext1] Is Not Null And [Ext2] Is Not Null And [Ext3] Is Null, [StartDate]+180,
         IIf([Ext1] Is Not Null And [Ext2] Is Not Null And [Ext3] Is Not Null, [StartDate]+210, 
             [StartDate])))

That doesn't take into account ALL possible combinations of Ext1, Ext2, Ext3, just an idea.

Wayne

 

[Wulf13]

Thank you

Thank you for this, I think this is what I've been looking for. I originally had date expired calculated in a query and the form pulled its info from the query but I couldn't figure out a formula to make it conditional and look at the 3 extensions. Looking at the one you came up with, I see how this could work.

I'll give it a shot and let you know, thanks again.