Add 3 months for next quarterly statement

  • Thread starter Thread starter Tom Ellison
  • Start date Start date
T

Tom Ellison

Dear Joe:

Shouldn't it be:

DateSerial(Year([Issue Date]), Month([Issue Date])+3),Day([Issue Date]))
 
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"));
 
consjoe said:
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"));
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Three months or 3 days?

3 months:

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

3 Days:

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

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

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzsOM4echKqOuFEgEQKwmgCgrkPcSq/uVFD4szLsdeZO4KvrKmkAnjRN
Jv3iWd04hG8M+bNFjGZ1dQfV
=M7qT
-----END PGP SIGNATURE-----
 
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 ...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzwWsYechKqOuFEgEQJGHQCgiWc0W9L/l6uu5cEmzk367bZbeoYAmgNi
eXmMa/g+0+Q4VLgyEvI1Dotf
=h7sk
-----END PGP SIGNATURE-----
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)


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
Click to expand...

((Master.Status)="ACTIVE"));
Click to expand...
 
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!

Tom Ellison said:
Dear Joe:

Shouldn't it be:

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

consjoe said:
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"));
Click to expand...
Click to expand...
 
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!

MGFoster said:
consjoe said:
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"));
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Three months or 3 days?

3 months:

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

3 Days:

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

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

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzsOM4echKqOuFEgEQKwmgCgrkPcSq/uVFD4szLsdeZO4KvrKmkAnjRN
Jv3iWd04hG8M+bNFjGZ1dQfV
=M7qT
-----END PGP SIGNATURE-----
Click to expand...
 
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



Van T. Dinh said:
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)


consjoe said:
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"));
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

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

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

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzya44echKqOuFEgEQIuFwCgpPbvUpm7EmAaey0muR1ZbTScaSoAoMl3
ao4SPaT/NJ6KrVAP1mZoAn/s
=Lnq4
-----END PGP SIGNATURE-----
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!

:

consjoe said:
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"));
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Three months or 3 days?

3 months:

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

3 Days:

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

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

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzsOM4echKqOuFEgEQKwmgCgrkPcSq/uVFD4szLsdeZO4KvrKmkAnjRN
Jv3iWd04hG8M+bNFjGZ1dQfV
=M7qT
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
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.
 
Back
Top