Date Range Clarification in Query

[Mark M Simonian]

I have a Crosstab query and am having trouble figuring out how to indicate a
date range that is 30 days beyond the date field called Anniversary.

This is the SQL:

TRANSFORM Count(tblMain.ID) AS CountOfID
SELECT tblMain.GroupName, tblMain.LastName, Sum(tblMain.Dues) AS [Total Of
Dues]
FROM tblMain
WHERE (((tblMain.Association)="Associate") AND
((tblMain.Anniversary)>DateAdd("yy",+30,[Anniversary])))
GROUP BY tblMain.GroupName, tblMain.LastName
PIVOT tblMain.Dues;


This part is incorrect and how would I change it?

DateAdd("yy",+30,[Anniversary])))

*********************
Mark M. Simonian MD FAAP
Medical Director, ChildNet Medical Associates
5305 N Fresno St 105A
Fresno, CA 93710
(559) 221-7192
(e-mail address removed)
www.markmsimonian.medem.com
*************************
Alert: This email and any files transmitted with it
are intended solely for the use of the individual or
entity to whom they are addressed and may contain
confidential, patient health or other legally
privileged information. If you have received this
email in error please notify the sender by email,
delete and destroy this message and its attachments.
Any unauthorized review, use, disclosure,
or distribution is prohibited.

 

[Van T. Dinh]

1. The IntervalString "yy" for the DateAdd is incorrect. Use either "d" or
"y".

2. The Boolean expression:

(tblMain.Anniversary)>DateAdd("d",30,[Anniversary]))

doesn't make any sense (since it always returns FALSE) as Anniversary (date)
can NEVER be greater (later) than itself + 30 days.

Plse explain your Boolean expression / condition clearly in words if you
need more help.

--
HTH
Van T. Dinh
MVP (Access)

I have a Crosstab query and am having trouble figuring out how to indicate a
date range that is 30 days beyond the date field called Anniversary.

This is the SQL:

TRANSFORM Count(tblMain.ID) AS CountOfID
SELECT tblMain.GroupName, tblMain.LastName, Sum(tblMain.Dues) AS [Total Of
Dues]
FROM tblMain
WHERE (((tblMain.Association)="Associate") AND
((tblMain.Anniversary)>DateAdd("yy",+30,[Anniversary])))
GROUP BY tblMain.GroupName, tblMain.LastName
PIVOT tblMain.Dues;


This part is incorrect and how would I change it?

DateAdd("yy",+30,[Anniversary])))

*********************
Mark M. Simonian MD FAAP
Medical Director, ChildNet Medical Associates
5305 N Fresno St 105A
Fresno, CA 93710
(559) 221-7192
(e-mail address removed)
www.markmsimonian.medem.com
*************************

 

[John Vinson]

This part is incorrect and how would I change it?

DateAdd("yy",+30,[Anniversary])))

To add 30 days, use "d" instead of "yy".

 

[Mark M Simonian]

Once I understood what works I felt silly. I agree it didn't make sense but
the following worked well.

in the Anniversary field of my Crosstab Query:
<DateAdd("m", +1, Date())
or
TRANSFORM Count(tblMain.ID) AS CountOfID
SELECT tblMain.GroupName, tblMain.LastName, Sum(tblMain.Dues) AS [Total Of
Dues]
FROM tblMain
WHERE (((tblMain.Association)="associate") AND
((tblMain.Anniversary)<DateAdd("m",+1,Date())))
GROUP BY tblMain.GroupName, tblMain.LastName
PIVOT tblMain.Dues;


*********************
Mark M. Simonian MD FAAP
Medical Director, ChildNet Medical Associates
5305 N Fresno St 105A
Fresno, CA 93710
(559) 221-7192
(e-mail address removed)
www.markmsimonian.medem.com
*************************
Alert: This email and any files transmitted with it
are intended solely for the use of the individual or
entity to whom they are addressed and may contain
confidential, patient health or other legally
privileged information. If you have received this
email in error please notify the sender by email,
delete and destroy this message and its attachments.
Any unauthorized review, use, disclosure,
or distribution is prohibited.

This part is incorrect and how would I change it?

DateAdd("yy",+30,[Anniversary])))

To add 30 days, use "d" instead of "yy".