G
Guest
Hello,
I have the following query that needs to be tweaked but I am at a loss as to
what to modify:
SELECT HOTLINE.[CALL DATE], HOTLINE.[CALL TIME], HOTLINE.[CALLER NAME],
HOTLINE.[HANG UP], HOTLINE.[CALLER PHONE NUMBER], HOTLINE.[CALL NUMBER],
HOTLINE.[OPERATING GROUP], HOTLINE.[Franchise Location], HOTLINE.[CALL CLOSE
DATE], HOTLINE.[CALL CLOSED], HOTLINE.[CALL CATEGORY], HOTLINE.[CALL ISSUE],
#6/30/2006#-[HOTLINE]![CALL DATE] AS [DAYS OPEN]
FROM HOTLINE
WHERE (((HOTLINE.[CALL DATE])<#7/1/2006#) AND ((HOTLINE.[HANG UP])=No) AND
((HOTLINE.[CALL CLOSED])=No)) OR (((HOTLINE.[CALL CLOSE DATE])>#7/1/2006#))
ORDER BY HOTLINE.[CALL DATE];
This is something we want to query based on quarter start and end dates.
What we want to accomplish is to have the query return any calls that were
open as of the last day of the current quarter (so in this case 6/30/06)
EXCLUDING any hang up calls. We also want to exclude closed calls UNLESS the
call date was after the end of the quarter (so anything that was received
[CALL DATE] before July 1st but closed [CALL CLOSE DATE] after July 1st).
Here are the issues I see:
We can't limit our received date to the current quarter or we will lose any
calls that carried over from the previous report (so we left the criteria as
"<#7/1/2006#"). That pulls what we want...
We left our criteria as "no" for the field [HANG UP], which works so far...
Herein lies the rub...our field [CALL CLOSED] is simply a yes/no check box
and is the only way to distinguish (without manual manipulation) between an
open or closed call. This is where we have problems. If we leave the
criteria as "no" we can't get the database to pull those closed calls that
would have been open as of 6/30/06. If we change the criteria to "yes" it
displays all calls received before 7/1/06 (including much older calls from
1998 since we can't limit our received date or lose the carry-overs). As we
attempted to manipulate the criteria for the [CALL CLOSED DATE], we found
that it then overrides the criteria for [CALL DATE] and pulls calls received
after the end of the quarter. We've talked this out several ways & just
can't seem to find the right syntax.
What are we missing?
We somehow got this to work last quarter, so we know it can be done but
unfortunately we failed to save the changes to the query as we are still
making changes to our reporting.
Any help would be greatly appreciated! Thank you!!!!!
I have the following query that needs to be tweaked but I am at a loss as to
what to modify:
SELECT HOTLINE.[CALL DATE], HOTLINE.[CALL TIME], HOTLINE.[CALLER NAME],
HOTLINE.[HANG UP], HOTLINE.[CALLER PHONE NUMBER], HOTLINE.[CALL NUMBER],
HOTLINE.[OPERATING GROUP], HOTLINE.[Franchise Location], HOTLINE.[CALL CLOSE
DATE], HOTLINE.[CALL CLOSED], HOTLINE.[CALL CATEGORY], HOTLINE.[CALL ISSUE],
#6/30/2006#-[HOTLINE]![CALL DATE] AS [DAYS OPEN]
FROM HOTLINE
WHERE (((HOTLINE.[CALL DATE])<#7/1/2006#) AND ((HOTLINE.[HANG UP])=No) AND
((HOTLINE.[CALL CLOSED])=No)) OR (((HOTLINE.[CALL CLOSE DATE])>#7/1/2006#))
ORDER BY HOTLINE.[CALL DATE];
This is something we want to query based on quarter start and end dates.
What we want to accomplish is to have the query return any calls that were
open as of the last day of the current quarter (so in this case 6/30/06)
EXCLUDING any hang up calls. We also want to exclude closed calls UNLESS the
call date was after the end of the quarter (so anything that was received
[CALL DATE] before July 1st but closed [CALL CLOSE DATE] after July 1st).
Here are the issues I see:
We can't limit our received date to the current quarter or we will lose any
calls that carried over from the previous report (so we left the criteria as
"<#7/1/2006#"). That pulls what we want...
We left our criteria as "no" for the field [HANG UP], which works so far...
Herein lies the rub...our field [CALL CLOSED] is simply a yes/no check box
and is the only way to distinguish (without manual manipulation) between an
open or closed call. This is where we have problems. If we leave the
criteria as "no" we can't get the database to pull those closed calls that
would have been open as of 6/30/06. If we change the criteria to "yes" it
displays all calls received before 7/1/06 (including much older calls from
1998 since we can't limit our received date or lose the carry-overs). As we
attempted to manipulate the criteria for the [CALL CLOSED DATE], we found
that it then overrides the criteria for [CALL DATE] and pulls calls received
after the end of the quarter. We've talked this out several ways & just
can't seem to find the right syntax.
What are we missing?
We somehow got this to work last quarter, so we know it can be done but
unfortunately we failed to save the changes to the query as we are still
making changes to our reporting.
Any help would be greatly appreciated! Thank you!!!!!