If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Use query criteria for calculated dates
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 |
#2
|
|||
|
|||
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 "Carol" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Problem using Query Criteria with an Expression | Carol | Running & Setting Up Queries | 6 | June 21st, 2005 09:03 PM |
improving performance by indexing query criteria fields | Paul James | Running & Setting Up Queries | 20 | February 16th, 2005 07:55 PM |
Moving from xBase/Clipper | [email protected] | New Users | 1 | February 3rd, 2005 07:25 PM |
Can't Open Data Source (Query) where criteria is defined by functi | l_stocky | Mailmerge | 7 | January 22nd, 2005 05:09 AM |