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
|
|||
|
|||
Excluding a day of the week (for only a short period of time)
I have the following critera built for a field in one of my Access queries.
1DaySurvivalCX: Sum(IIf( ([EnterpriseName] In ("CSR_Cancel_Request","CSR_Cancel_Request_Rollover ","Queue") Or [EnterpriseName] In ("NULL","") And [Ticket_Header] In ("Automatic comment - Cancel Save Attempt","Cancel Team") Or [EnterpriseName] Is Null And [Ticket_Header] In ("Automatic comment - Cancel Save Attempt","Cancel Team")) And [One_Day_Survival]=1,[One_Day_Survival],0)) This currently sums the survivials of a records based on the criteria of the EnterpriseName or Ticket_Header. Now I need to determine the survivals exclusive of Sundays but only until July 1, 2010 when I then need to automatically include records from Sunday. My though was to add something like this into the existing critera: (Date[Ticket_Created_Date ]#7/1/2010# and Weekday([Ticket_Created_Date ] in (2,3,4,5,6,7)) but no matter how I add it, the criteria continues to include the sum of the Sunday survivals. I think I have the right concept, but would appreciate further input on how to make this work. Thanks in advance... |
#2
|
|||
|
|||
Excluding a day of the week (for only a short period of time)
gillah11 wrote:
I have the following critera built for a field in one of my Access queries. 1DaySurvivalCX: Sum(IIf( ([EnterpriseName] In ("CSR_Cancel_Request","CSR_Cancel_Request_Rollove r","Queue") Or [EnterpriseName] In ("NULL","") And [Ticket_Header] In ("Automatic comment - Cancel Save Attempt","Cancel Team") Or [EnterpriseName] Is Null And [Ticket_Header] In ("Automatic comment - Cancel Save Attempt","Cancel Team")) And [One_Day_Survival]=1,[One_Day_Survival],0)) This currently sums the survivials of a records based on the criteria of the EnterpriseName or Ticket_Header. Now I need to determine the survivals exclusive of Sundays but only until July 1, 2010 when I then need to automatically include records from Sunday. My though was to add something like this into the existing critera: (Date[Ticket_Created_Date ]#7/1/2010# and Weekday([Ticket_Created_Date ] in (2,3,4,5,6,7)) but no matter how I add it, the criteria continues to include the sum of the Sunday survivals. You must have retyped whatever you actually tried because that has so many syntax errors, you would not be allowed to run it. I think you want to use something more like: Sum( IIf( ( ([EnterpriseName] In("CSR_Cancel_Request", "CSR_Cancel_Request_Rollover", "Queue") Or [EnterpriseName] In("NULL","") this does not look right ) And ( [Ticket_Header] In("Automatic comment - Cancel Save Attempt", "Cancel Team") Or ( [EnterpriseName] Is Null And [Ticket_Header] In("Automatic comment - Cancel Save Attempt", "Cancel Team") ) ) And [One_Day_Survival]=1 And ( ( [Ticket_Created_Date ] #7/1/2010# And Weekday([Ticket_Created_Date]) 1 ) Or [Ticket_Created_Date ] = #7/1/2010# ) , [One_Day_Survival], 0) ) That is a real mess, but I tried to write it in such a way to make it a little easier to count the parenthesis (may still not be right). I have to think that there must be a better way to all that. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Excluding a day of the week (for only a short period of time)
Perhaps something like the following.
SUM(IIF(EnterpriseName & "" IN("","CSR_Cancel_Request","CSR_Cancel_Request_Rol lover","Queue") AND Ticket_Header in ("Automatic comment - Cancel Save Attempt","Cancel Team") ,IIF(Ticket_Created_Date#7/1/2010 and WeekDay(Ticket_Created_Date) 1,SOME VALUE,IIF(Ticket_Created_Date =#7/1/2010#,SOME VALUE,Null)),Null)) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County gillah11 wrote: I have the following critera built for a field in one of my Access queries. 1DaySurvivalCX: Sum(IIf( ([EnterpriseName] In ("CSR_Cancel_Request","CSR_Cancel_Request_Rollover ","Queue") Or [EnterpriseName] In ("NULL","") And [Ticket_Header] In ("Automatic comment - Cancel Save Attempt","Cancel Team") Or [EnterpriseName] Is Null And [Ticket_Header] In ("Automatic comment - Cancel Save Attempt","Cancel Team")) And [One_Day_Survival]=1,[One_Day_Survival],0)) This currently sums the survivials of a records based on the criteria of the EnterpriseName or Ticket_Header. Now I need to determine the survivals exclusive of Sundays but only until July 1, 2010 when I then need to automatically include records from Sunday. My though was to add something like this into the existing critera: (Date[Ticket_Created_Date ]#7/1/2010# and Weekday([Ticket_Created_Date ] in (2,3,4,5,6,7)) but no matter how I add it, the criteria continues to include the sum of the Sunday survivals. I think I have the right concept, but would appreciate further input on how to make this work. Thanks in advance... |
#4
|
|||
|
|||
Excluding a day of the week (for only a short period of time)
Thanks Marshall. Yes my criteria is a mess and a result of the messy data we
get from the group that provides it and the only way I could accomdate everything that needed to be worked around for the result we need to come up with. There probably is a better way to do it, but I don't get any syntax errors at all when I run it and my QA results are spot on... until I have to mix it up further with the newest date critera required. I will try your suggestion below and see what I come up with. I appreciate your input, it's always good to get another perspective. Thanks! "Marshall Barton" wrote: gillah11 wrote: I have the following critera built for a field in one of my Access queries. 1DaySurvivalCX: Sum(IIf( ([EnterpriseName] In ("CSR_Cancel_Request","CSR_Cancel_Request_Rollove r","Queue") Or [EnterpriseName] In ("NULL","") And [Ticket_Header] In ("Automatic comment - Cancel Save Attempt","Cancel Team") Or [EnterpriseName] Is Null And [Ticket_Header] In ("Automatic comment - Cancel Save Attempt","Cancel Team")) And [One_Day_Survival]=1,[One_Day_Survival],0)) This currently sums the survivials of a records based on the criteria of the EnterpriseName or Ticket_Header. Now I need to determine the survivals exclusive of Sundays but only until July 1, 2010 when I then need to automatically include records from Sunday. My though was to add something like this into the existing critera: (Date[Ticket_Created_Date ]#7/1/2010# and Weekday([Ticket_Created_Date ] in (2,3,4,5,6,7)) but no matter how I add it, the criteria continues to include the sum of the Sunday survivals. You must have retyped whatever you actually tried because that has so many syntax errors, you would not be allowed to run it. I think you want to use something more like: Sum( IIf( ( ([EnterpriseName] In("CSR_Cancel_Request", "CSR_Cancel_Request_Rollover", "Queue") Or [EnterpriseName] In("NULL","") this does not look right ) And ( [Ticket_Header] In("Automatic comment - Cancel Save Attempt", "Cancel Team") Or ( [EnterpriseName] Is Null And [Ticket_Header] In("Automatic comment - Cancel Save Attempt", "Cancel Team") ) ) And [One_Day_Survival]=1 And ( ( [Ticket_Created_Date ] #7/1/2010# And Weekday([Ticket_Created_Date]) 1 ) Or [Ticket_Created_Date ] = #7/1/2010# ) , [One_Day_Survival], 0) ) That is a real mess, but I tried to write it in such a way to make it a little easier to count the parenthesis (may still not be right). I have to think that there must be a better way to all that. -- Marsh MVP [MS Access] . |
Thread Tools | |
Display Modes | |
|
|