VBA Code for dates

hilbertm

Registered User.
Local time
Today, 23:58
Joined
Sep 7, 2001
Messages
46
I am not very experienced with VBA and I could use some help

I need to figure out how to code the following situation:
I have a publication that is published every 56days. There is a cutoff date to get changes into the publisher to make the next cycle. The cutoff date is 27 days before the publication date .

I have a form with the following dates in txt boxes:

I have one txt box on the form that is bound to the field [FlipEffDateStart]

All the other dates on the form are figured using the [FlipEffDateStart] field.

The current Publication effective date is calculated by [FlipEffDateStart] –56
The current Publication cutoff date is calculated by [FlipEffDateStart] –83
The Next Publication effective date is the field [FlipEffDateStart]
The Next Publication cutoff date is calculated by [FlipEffDateStart] –27
The Next x2 Publication date is calculated by [FlipEffDateStart] + 56
The Next x2 Publication Cutoff date is calculated by [FlipEffDateStart] +83

What I want is for the dates to automatically update when the next publication date is reached.
I also need a message to show on the screen updating the users how much time is left before the next cutoff date. EX.(“22 days left before cutoff”).

When there are 3 days to the cutoff I want the text to turn red


When the cutoff date is reached, but the next publication date has not been reached yet, I need a message that says “the cutoff for this cycle has passed. Changes sent in now will not make this publication cycle.”

When the next publication effective date is reached, I need to reset the next publication effective date to date + 56 and update the message to tell how much time is left before the next cutoff date.

I also have a table with the following fields:
FlipEffDateStart
FlipCutoffDate
NextFlipEffDate
NextFlipCutoffDate
FlipCutoffDate
FlipEffDateStart

Right now, I am just using the FlipEffDateStart field of this table. I'm sure this should be easy, but I can' t seem to figure it out.

Thanks for any help.
 
Hi

Firstly, I have to say I find this a mite confusing!! anyway, as nobody else has posted I'll start it off.

In your table, you have 6 fields - 2 of which appear to be named twice (?) not sure about this bit

Now as for this date thing, I'll change the field names to help me understand. You have a publishing date [PublishDate] and you have a cut-off date [CutOffDate] which is [PublishDate] - 27. So if the DateDiff between today and [PublishDate] is > 27 then all is well, if its < 27 then no more articles can be submitted.

So basically you need to use the [PublishDate] as your "base date" and calculate how many days to go to that using a DateDiff function then depending on the result that will depend on what message is put on screen. You can get those dates to appear on a form by using the OnCurrent event.

There's got to be more to it but post back if you need to. This may start you along the right road.

Col
 
There's problems with the values being used to find future pub and cutoff dates.

Putting your fields in chronological order -

The current Publication cutoff date is calculated by [FlipEffDateStart] –83
The current Publication effective date is calculated by [FlipEffDateStart] –56
The Next Publication cutoff date is calculated by [FlipEffDateStart] –27
The Next Publication effective date is the field [FlipEffDateStart]
The Next x2 Publication date is calculated by [FlipEffDateStart] + 56
The Next x2 Publication Cutoff date is calculated by [FlipEffDateStart] +83

The highlighted line can't be right: a publication cutoff date occurs before the effective date, right?

So let's make the order like so:

The current Publication cutoff date is calculated by [FlipEffDateStart] –83
The current Publication effective date is calculated by [FlipEffDateStart] –56
The Next Publication cutoff date is calculated by [FlipEffDateStart] –27
The Next Publication effective date is the field [FlipEffDateStart]
The Next x2 Publication Cutoff date is calculated by [FlipEffDateStart] +27
The Next x2 Publication date is calculated by [FlipEffDateStart] + 56

That's still not right. Why? Because adding 27 to a number (an Access date) is not the same as adding 56 to a number and subtracting 27. 56 - 27 = 29

Your field list should be:

The current Publication cutoff date is calculated by [FlipEffDateStart] –83
The current Publication effective date is calculated by [FlipEffDateStart] –56
The Next Publication cutoff date is calculated by [FlipEffDateStart] –27
The Next Publication effective date is the field [FlipEffDateStart]
The Next x2 Publication Cutoff date is calculated by [FlipEffDateStart] +29
The Next x2 Publication date is calculated by [FlipEffDateStart] + 56

HTH,

Doug.
 

Users who are viewing this thread

Back
Top Bottom