T
Tom Ellison
Dear Joe:
Shouldn't it be:
DateSerial(Year([Issue Date]), Month([Issue Date])+3),Day([Issue Date]))
Shouldn't it be:
DateSerial(Year([Issue Date]), Month([Issue Date])+3),Day([Issue Date]))
C
consjoe
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"));
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"));
M
MGFoster
consjoe said:
-----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-----
V
Van T. Dinh
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 ...
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 ...
M
MGFoster
-----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-----
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-----
C
consjoe
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!
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:
C
consjoe
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 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:
M
Michel Walsh
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
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:
M
MGFoster
-----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-----
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-----
V
Van T. Dinh
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.
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.
V
Van T. Dinh
Thanks, Michel.