Excluding a day of the week (for only a short period of time)

  • Thread starter Thread starter gillah11
  • Start date Start date
G

gillah11

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...
 
Perhaps something like the following.

SUM(IIF(EnterpriseName & ""
IN("","CSR_Cancel_Request","CSR_Cancel_Request_Rollover","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
 
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 said:
gillah11 said:
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.
Click to expand...

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.
Click to expand...
 
Back
Top