Add 3 months for next quarterly statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

consjoe - 28 Sep 2005 22:28 GMT

I am sending out quarterly statements for my customers and after the
statements go out I need to update the Send Statement date for the next
quarter. I need the Day to always be the Issue Date day and the month to add
3 everytime a statement is sent out. I also need to make sure the year will
update when appropriate.
Here is the SQL I have for my query but I can't get it to work right.
Can you let me know what I am doing wrong.
Thank You.

UPDATE Master SET Master.[Send Statement] =
DateSerial(Year(Date()),Month(Date()+3),Day([Issue Date]))
WHERE (((Master.[Quarterly Statement])=Yes) AND ((Master.Status)="ACTIVE"));

Reply to this Message

Tom Ellison - 27 Sep 2005 22:42 GMT

Dear Joe:

Shouldn't it be:

DateSerial(Year([Issue Date]), Month([Issue Date])+3),Day([Issue Date]))

Signature

Tom Ellison

> I am sending out quarterly statements for my customers and after the
> statements go out I need to update the Send Statement date for the next
[quoted text clipped - 8 lines]
> DateSerial(Year(Date()),Month(Date()+3),Day([Issue Date]))
> WHERE (((Master.[Quarterly Statement])=Yes) AND ((Master.Status)="ACTIVE"));

Reply to this Message

consjoe - 29 Sep 2005 18:30 GMT

If I just go off the Issue Date + 3 that will only give me the first quarter
letter. What about second Issue Date Month + 6, third Issued Date Month + 9,
and forth Issue Date Month or + 12?
Also if a contract is suspended for 2 quarters and then becomes active again
I need to make sure a statement is sent out for the next quarter. This way
it will ignore the contract when status <> active but as soon as status =
active, the quarterly statement process needs to continue from that point
forward.
Thanks!

> Dear Joe:
>
[quoted text clipped - 16 lines]
> > WHERE (((Master.[Quarterly Statement])=Yes) AND
> ((Master.Status)="ACTIVE"));

Reply to this Message

MGFoster - 28 Sep 2005 22:42 GMT

Three months or 3 days?

3 months:

SET [Send Statement] = DateAdd("m", 3, Date())

3 Days:

SET [Send Statement] = DateAdd("d", 3, Date())

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Reply to this Message

consjoe - 29 Sep 2005 18:34 GMT

Okay I will try that. Thank you.
I have one other question that I forgot to add the first time. I replied to
Tom also with the same question but I didn't know if you would check back
unless you had a notification of a reply.
If a contract is suspended for (an example)2 quarters and then becomes
active again I need to make sure a statement is sent out for the next
quarter. This way it will ignore the contract when status <> active but as
soon as status = active, the quarterly statement process needs to continue
from that point forward staying on the 3 month intervals of the Issue Date.
Thanks!

> > I am sending out quarterly statements for my customers and after the
> > statements go out I need to update the Send Statement date for the next
[quoted text clipped - 21 lines]
>
> SET [Send Statement] = DateAdd("d", 3, Date())

Reply to this Message

MGFoster - 30 Sep 2005 02:54 GMT

If you just want to run a statement every 3 months you can use the MOD
function:

WHERE Month([issue date]) mod 3 = 0

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Okay I will try that. Thank you.
> I have one other question that I forgot to add the first time. I replied to
[quoted text clipped - 32 lines]
>>
>>SET [Send Statement] = DateAdd("d", 3, Date())

Reply to this Message

Van T. Dinh - 29 Sep 2005 09:01 GMT

Try:

UPDATE Master
SET Master.[Send Statement] =
DateSerial("m", 3, [Issue Date])
WHERE (((Master.[Quarterly Statement])=True)
AND ((Master.Status)="ACTIVE"));

you need to work out what you want to do if the date is 30/11/2005 and
adding 3 months will NOT fall on the same day of the month, i.e.
"30/02/2006" doesn't exist. There are similar problems for other
end-of-month dates.

IMHO, the requirement for the same day of the month is rather illogical and
you will need to clarify it with whoever has the authority ...

--
HTH
Van T. Dinh
MVP (Access)

Reply to this Message

MGFoster - 29 Sep 2005 17:30 GMT

Van, I think you need to get some sleep (your post has 1AM on my
server). You incorrectly used DateSerial(), when I believe you meant to
use DateAdd().

Also, adding 3 months to 30/11/2005 will yield 28/02/2006 not
30/02/2006.

Debug window:

? dateadd("m",3,#11/30/2005#)
2/28/2006

Rgds,

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Try:
>
[quoted text clipped - 37 lines]
>
> ((Master.Status)="ACTIVE"));

Reply to this Message

Van T. Dinh - 30 Sep 2005 05:58 GMT

Thanks for the correction. I did mean DateAdd().

1:00am is usually not the problem for me but I am actually in the U.S. at
present and the time difference threw everything out and I am actually
sleep-deprived for more than 48 hours when I posted ... I am not even sure
my post got the correct date/time???

OTOH, the DateAdd still give the wrong "day of the month" which was
specified by the original post. I didn't test but I knew that "30/02/2006"
(the double-quotes were also in my earlier reply) was impossible.

--
Cheers
Van

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
[quoted text clipped - 23 lines]
> eXmMa/g+0+Q4VLgyEvI1Dotf
> =h7sk

Reply to this Message

Michel Walsh - 29 Sep 2005 21:02 GMT

Hi,

DateAdd seems to be safe:

? DateAdd("m", 3, "2005 August 31")
2005.11.30

and not the 31st of November.

But I don't see the complete thread... so I may not get the whole picture
about what you are saying... :-)

Vanderghast, Access MVP

> Try:
>
[quoted text clipped - 33 lines]
>> WHERE (((Master.[Quarterly Statement])=Yes) AND
> ((Master.Status)="ACTIVE"));

Reply to this Message

Van T. Dinh - 30 Sep 2005 06:01 GMT

Thanks, Michel.

--
HTH
Van T. Dinh
MVP (Access)

> Hi,
>
[quoted text clipped - 9 lines]
>
> Vanderghast, Access MVP

Reply to this Message