Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Location
    Baton Rouge
    Posts
    4

    Question Unanswered: year seperating data

    this worked until i had more than a years worth of data. now this and other quereies are pulling data from last year and this year together. i would like to be able to use the calandar select form's year value to have some meaning.
    here is my SQL code:
    SELECT Incident.StartDate, Incident.StartTime, Incident.IncidentIDNumber, Incident.ClearDate, Incident.ClearTime, Incident.ClosedLanes, IIf(([starttime] Between "05:30" And "09:30") And (CDate([ClearDate] & " " & [ClearTime])>CDate([StartDate] & " " & [StartTime])),1,0) AS AMRHLBIncident, IIf(([starttime] Between "05:30" And "09:30"),1,0) AS AMRHIncident, IIf(([starttime] Between "15:30" And "18:30") And (CDate([ClearDate] & " " & [ClearTime])>CDate([StartDate] & " " & [StartTime])),1,0) AS PMRHLBIncident, IIf(([starttime] Between "15:30" And "18:30"),1,0) AS PMRHIncident, IIf(([Closedlanes]>0),1,0) AS BlockedLnIncident, IIf(([CauseType]="multi vehicle") Or ([CauseType]="Disabled Vehicle") Or ([CauseType]="Police/Medical Activity") Or ([CauseType]="Debris") Or ([CauseType]="1 Vehicle") Or ([CauseType]="Overturned Vehicle") Or ([CauseType]="Vehcile Fire") Or ([CauseType]="Vehicle Fire"),1,0) AS ALLINCIDENTS
    FROM Incident
    WHERE (((Incident.StartDate) Between Forms!SelectDateDialog!BeginningDate And Forms!SelectDateDialog!EndingDate));

  2. #2
    Join Date
    Sep 2006
    Location
    Baton Rouge
    Posts
    4

    lack of cdate

    changed WHERE portion of SQL:
    SELECT Incident.StartDate, Incident.StartTime, Incident.IncidentIDNumber, Incident.ClearDate, Incident.ClearTime, Incident.ClosedLanes, IIf(([starttime] Between "05:30" And "09:30") And (CDate([ClearDate] & " " & [ClearTime])>CDate([StartDate] & " " & [StartTime])),1,0) AS AMRHLBIncident, IIf(([starttime] Between "05:30" And "09:30"),1,0) AS AMRHIncident, IIf(([starttime] Between "15:30" And "18:30") And (CDate([ClearDate] & " " & [ClearTime])>CDate([StartDate] & " " & [StartTime])),1,0) AS PMRHLBIncident, IIf(([starttime] Between "15:30" And "18:30"),1,0) AS PMRHIncident, IIf(([Closedlanes]>0),1,0) AS BlockedLnIncident, IIf(([CauseType]="multi vehicle") Or ([CauseType]="Disabled Vehicle") Or ([CauseType]="Police/Medical Activity") Or ([CauseType]="Debris") Or ([CauseType]="1 Vehicle") Or ([CauseType]="Overturned Vehicle") Or ([CauseType]="Vehcile Fire") Or ([CauseType]="Vehicle Fire"),1,0) AS ALLINCIDENTS
    FROM Incident
    WHERE ((cdate((Incident.StartDate)) Between cdate(Forms!SelectDateDialog!BeginningDate) And cdate(Forms!SelectDateDialog!EndingDate)));
    Last edited by bcoste1; 05-06-08 at 11:59.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •