Use query criteria for calculated dates

[Carol]

I have a database of records with expected (Expr1) and final (Expr2) due
dates. These dates are calculated with the Dateadd function and calculate
correctly.

I'm trying to create queries (and reports based off the queries) that will
do the following:
1. Enter the section name and Range of dates that records are expected due
(Expr1).
2. Enter range of dates that records are expected due.
3. Enter the section name and any records with a final due date (Expr2)
less than the current date ( <date() ).
4. Return any records with a final due date (Expr 2) less than the current
date ( <date() ).
I've copied the SQL from query #2 above.
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT MAIN.ID, MAIN.DEPT, MAIN.SECTION, MAIN.TASK, MAIN.[PROF MAIL DATE],
MAIN.[EST TIME (HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE
PARTY], MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL],
MAIN.[FREQ NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER],
DateAdd([CATEGORY INTERVAL],[CATEGORY NUMBER],DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE])) AS Expr2
FROM MAIN
WHERE (((MAIN.DEPT)="LAB") AND ((MAIN.[FREQ INTERVAL]) Is Not Null) AND
((MAIN.[FREQ NUMBER]) Is Not Null) AND ((MAIN.[CATEGORY INTERVAL]) Is Not
Null) AND ((MAIN.[CATEGORY NUMBER]) Is Not Null));

I have entered the criteria in design view but it's not returning the
correct info. I am able to enter the section name (query 1 and 3), however,
when I enter the date ranges on the calculated fields, it returns all records
in the database. How do I restrict it to just the date range I enter or <
than the current date?
Thanks,
Carol

 

[[MVP] S.Clark]

Using an OR condition is a little tricky when you need to incorporate other
AND conditions with it.

Thus, one of the following may apply, depending on your situation:

WHERE A OR (B AND C AND D)
WHERE (A AND C AND D) OR B
WHERE (A OR B) AND C AND D

Replace A and B with your date needs and C and D as all the Is Not Nulls.

 

[Carol]

Steve,
I got all my reports to work except 1....This report will return all records
that have a final due date (Expr2) less than the current date. I copied the
SQL below. What do I do to get it to work; I'm at a loss.
Thanks,
Carol

SELECT MAIN.ID, MAIN.DEPT, MAIN.SECTION, MAIN.TASK, MAIN.[PROF MAIL DATE],
MAIN.[EST TIME (HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE
PARTY], MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL],
MAIN.[FREQ NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER],
DateAdd([CATEGORY INTERVAL],[CATEGORY NUMBER],DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE])) AS Expr2
FROM MAIN
WHERE (((MAIN.DEPT)="LAB") AND ((MAIN.[FREQ INTERVAL]) Is Not Null) AND
((MAIN.[FREQ NUMBER]) Is Not Null) AND ((MAIN.[CATEGORY INTERVAL]) Is Not
Null) AND ((MAIN.[CATEGORY NUMBER]) Is Not Null) AND ((DateAdd([CATEGORY
INTERVAL],[CATEGORY NUMBER],DateAdd([FREQ INTERVAL],[FREQ NUMBER],[LAST
COMPLETED DATE])))<[Enter Current Date]));

Using an OR condition is a little tricky when you need to incorporate other
AND conditions with it.

Thus, one of the following may apply, depending on your situation:

WHERE A OR (B AND C AND D)
WHERE (A AND C AND D) OR B
WHERE (A OR B) AND C AND D

Replace A and B with your date needs and C and D as all the Is Not Nulls.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a database of records with expected (Expr1) and final (Expr2) due
dates. These dates are calculated with the Dateadd function and calculate
correctly.

I'm trying to create queries (and reports based off the queries) that will
do the following:
1. Enter the section name and Range of dates that records are expected
due
(Expr1).
2. Enter range of dates that records are expected due.
3. Enter the section name and any records with a final due date (Expr2)
less than the current date ( <date() ).
4. Return any records with a final due date (Expr 2) less than the
current
date ( <date() ).
I've copied the SQL from query #2 above.
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT MAIN.ID, MAIN.DEPT, MAIN.SECTION, MAIN.TASK, MAIN.[PROF MAIL DATE],
MAIN.[EST TIME (HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE
PARTY], MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL],
MAIN.[FREQ NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER],
DateAdd([CATEGORY INTERVAL],[CATEGORY NUMBER],DateAdd([FREQ
INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE])) AS Expr2
FROM MAIN
WHERE (((MAIN.DEPT)="LAB") AND ((MAIN.[FREQ INTERVAL]) Is Not Null) AND
((MAIN.[FREQ NUMBER]) Is Not Null) AND ((MAIN.[CATEGORY INTERVAL]) Is Not
Null) AND ((MAIN.[CATEGORY NUMBER]) Is Not Null));

I have entered the criteria in design view but it's not returning the
correct info. I am able to enter the section name (query 1 and 3),
however,
when I enter the date ranges on the calculated fields, it returns all
records
in the database. How do I restrict it to just the date range I enter or <
than the current date?
Thanks,
Carol