 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Stuck on an update to a Query
I have a current query and the user is requesting an change by adding only Event.Status that are "Open", only for the StartCount records. I can't seem to get it right. Thank you for the help! PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime; SELECT Event.[Event Type], Event.Priority, DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority] = '" & [Priority] & "' AND [Received Date] < #" & [Enter Start Date] & "#") AS StartCount, Sum(IIf(Event.Status="Open",1,0)) AS NbrRecd, DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority] = '" & [Priority] & "' AND [Close Date] < #" & [Enter End Date]+1 & "# AND [Close Date] >= #" & [Enter Start Date] & "#") AS NbrClosed FROM Event WHERE (((Event.[Received Date])>=[Enter Start Date] And (Event.[Received Date])<[Enter End Date]+1) AND ((Event.[Event Reference]) Is Not Null)) OR (((Event.[Received Date])>#12/31/2099#) AND ((Event.[Event Reference]) Is Not Null)) GROUP BY Event.[Event Type], Event.Priority; >I have a current query and the user is requesting an change by adding only >Event.Status that are "Open", only for the StartCount records. I can't seem [quoted text clipped - 20 lines] > >GROUP BY Event.[Event Type], Event.Priority; Could you explain the problem? What do you mean by "the StartCount records" - StartCount is a calculated field; what do you want to do with that field?  SignatureJohn W. Vinson [MVP] Hi John, For the calculted field of StartCount, I need to include only those records that have an Event.Status of "open". Currently it is retrieving all records, regardless of the Status, but I opnly want to count the Open records. Thanks Mike > >I have a current query and the user is requesting an change by adding only > >Event.Status that are "Open", only for the StartCount records. I can't seem [quoted text clipped - 23 lines] > Could you explain the problem? What do you mean by "the StartCount records" - > StartCount is a calculated field; what do you want to do with that field? >Hi John, > [quoted text clipped - 3 lines] >Thanks >Mike Then just include that criterion in the DCount function calls: PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime; SELECT Event.[Event Type], Event.Priority, DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority] = '" & [Priority] & "' AND [Received Date] < #" & [Enter Start Date] & "# AND [Status] = 'Open'") AS StartCount, Sum(IIf(Event.Status="Open",1,0)) AS NbrRecd, DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority] = '" & [Priority] & "' AND [Close Date] < #" & [Enter End Date]+1 & "# AND [Close Date] >= #" & [Enter Start Date] & "#") AS NbrClosed FROM Event WHERE (((Event.[Received Date])>=[Enter Start Date] And (Event.[Received Date])<[Enter End Date]+1) AND ((Event.[Event Reference]) Is Not Null)) OR (((Event.[Received Date])>#12/31/2099#) AND ((Event.[Event Reference]) Is Not Null)) GROUP BY Event.[Event Type], Event.Priority; Note that I'm using ' rather than " as a delimiter for the 'Open' criterion, since the string is already within " delimiters. SignatureJohn W. Vinson [MVP] Thanks! > >Hi John, > > [quoted text clipped - 30 lines] > Note that I'm using ' rather than " as a delimiter for the 'Open' criterion, > since the string is already within " delimiters. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.