query date and time criteria problem

[kidrobot]

I have to run a query where the criteria is specific time ranges. The column is formatted as date/time (ie. 7/12/2008 06:28:02). I need the criteria to give me data for specific time ranges on each individual day for a year. So for the data set of 7/01/2007-12/31/2007 I need the specific data for 6am - 10am, then the data for 10am-7pm, then the data for 7pm-10pm, finally the data for 10-6pm. Let me know if this doesnt make sense!

 

[kidrobot]

i believe i need an iif... so like ill have a field with the criteria between '7/1/2007 and 12/31/2007' and another field with some time of iif between 6am - 10am count that field? correct? little help with the code please!!

 

[namliam]

So what you want to do is group 6,7,8,9 and 10am into one group 6-10am yes that makes sence...
You can use hour(YourDateField) to extract the our out of your datefield.

You can do this a couple of ways.... but I think this way or something simular is close to what you are looking for...
int(Hour(yourDateField) / 5)*5 +1 & " - " & (int(Hour(yourDateField) / 5)+1) *5

 

[kidrobot]

thankyou thankyou very much for your help. I tried your expression and had the hour ranges of 1-5, 11-15, 16-20, 21-25, 6-10. I need 6-10, 10-19, 19-22, 22-6

 

[Brianwarnock]

Ok the dates are taken care of using criteria in the normal way , then thevariuos time counts can be done with several fields like so
Sum(IIf(Format([datetime],"hh:nn:ss")<#10:00:00# And Format([datetime],"hh:nn:ss")>=#06:00:00#,1,0))

Brian

 

[Brianwarnock]

HMM the Mailman's approach looks interesting, might have to test that.

Brian

 

[kidrobot]

Brian thanks for the help... your function gave me an error of invalid syntax

One more thing I just noticed Saturday and Sunday have different times than Mon - Fri. for Sat and Sun the times are 6am - 2pm; 2pm - 10pm; 10pm-6am

 

[Brianwarnock]

Are you coding in the design grid or SQL, if sql then a date is needed #12/30/1899 7:0:0# for example.

I hve run the code against a db.

Brian

 

[kidrobot]

namliam, thankyou thankyou very much for your help. I tried your expression and had the hour ranges of 1-5, 11-15, 16-20, 21-25, 6-10. I need 6-10, 10-19, 19-22, 22-6. Is this possible since the time intervals aren't exactly at a constant increase?

 

[kidrobot]

Are you coding in the design grid or SQL, if sql then a date is needed #12/30/1899 7:0:0# for example.

I hve run the code against a db.

Brian

Brian, I got your code to work, but I changed it to this. Would this work, because the Counts I'm getting look very incorrect...

Expr1: Count(IIf(Format([IngateStartDT],"hh:nn:ss")<#10:00:00 AM# And Format([IngateStartDT],"hh:nn:ss")>=#6:00:00 AM#,1,0))

 

[Brianwarnock]

Count wont work like that, Count(*) and then using criteria will work but only for one scenario per query. What Sum(IIF(criteria,1,0)) does is add a field of value 1 to the records then Sums those ones, thus the Totals query can have many such fields.

Don't know why you had to put the AM in.

This sql ran ok for me
SELECT Table1.id, Sum(IIf(Format([datetime],"hh:nn:ss")<#12/30/1899 8:0:0# And Format([datetime],"hh:nn:ss")>=#12/30/1899 7:0:0#,1,0)) AS countid
FROM Table1
WHERE (((Table1.datetime) Is Not Null))
GROUP BY Table1.id;

I had used the design grid with this statement

countid: Sum(IIf(Format([datetime],"hh:nn:ss")<#08:00:00# And Format([datetime],"hh:nn:ss")>=#07:00:00#,1,0))


Brian

 

[kidrobot]

Brian this is what I have ...

SELECT DISTINCT Format([IngateStartDT],'mmmm') AS Mon, Format([IngateStartDT],'dddd') AS [Day], 
Sum(IIf(Format([IngateStartDT],"hh:nn:ss")<#12/30/1899 10:0:0# And Format([IngateStartDT],"hh:nn:ss")>=#12/30/1899 6:0:0#,1,0)) AS CountId, 
Count(*) AS Expr1

FROM [T OLDDATA]

WHERE ((([T OLDDATA].IngateStartDT) Between #7/1/2007# And #7/31/2007#))

GROUP BY Format([IngateStartDT],'mmmm'), Format([IngateStartDT],'dddd');

Although, CountId shows up as Zeros and my count(*) is still counting all of my data and not the data between 6am-10am

 

[Brianwarnock]

The only notion I can come up with and i'm sure this cannot be right is that there is no data between those times on those dates.

Brian

 

[kidrobot]

Ok the time criteria was the issue. I switched the 10:00 and 6:00 positions and the query worked... but the data is incorrect because it pulls less than 6:00 and more than 10:00, it wont work as more than 6 and less than 10... why is that??

 

[namliam]

Hmz, my quick look had thought it was even 5 hours everytime... my mistake....

Yes you will either have to resort to a IIF, Function or Related table...
IIF:
iif(YourDatetimefield >= 22 or Hour(now()+6/24) < 6, "22-06",
iif(YourDatetimefield<10, "06-10",
iif(YourDatetimefield<19, "10-19", "19-22")))

Function:

Function TimeFrame(YourDateTimeField As Date) As String
    Select Case Hour(YourDateTimeField)
        Case 0, 1, 2, 3, 4, 5, 22, 23
            TimeFrame = "22-06"
        Case 6, 7, 8, 9
    '... etc...
    End Select
End Function

You would call this function in your query: TimeFrame(YourDatetimefield)

Related table:
1) make a query first that has a field Hour(YourDatetimefiled) in it. Save it.
2) create a table with Hours 0 thru 23 and your periods that you want.
3) Now make a query to link 1 and 2 together to get your periods.

Offcourse making the related table on something like this is the prefered way of doing things like this. If you ever need to change your periods because " management thinks it is usefull" then it would be easy with this related table. Hardcoded IIFs are A FRIGGIN' pain... use that as a last resort. IIF tho is the fastest way with less "overhead", but in the long run Related table> Function > IIF.

Good Luck!

 

[kidrobot]

namliam,

your help has been amazing. I'm trying to do it the IIF way

iif(IngateStartDT >= 22 or Hour(now()+6/24) < 6, "22-06",
iif(IngateStartDT<10, "06-10",
iif(IngateStartDT<19, "10-19", "19-22")))

although my results will only show the 22-06

Here is my query

SELECT Format([IngateStartDT],'mmmm') AS Mon, 
Format([IngateStartDT],'dddd') AS [Day], 
IIf([IngateStartDT]>=22 Or Hour(Now()+6/24)<6,"22-06",IIf([IngateStartDT]<10,"06-10",IIf([IngateStartDT]<19,"10-19","19-22"))) AS IIF, Count([T OLDDATA].VisitID) AS CountOfVisitID

FROM [T OLDDATA]
WHERE ((([T OLDDATA].IngateStartDT) Between #7/1/2007# And #7/31/2007#))

GROUP BY Format([IngateStartDT],'mmmm'), Format([IngateStartDT],'dddd'), IIf([IngateStartDT]>=22 Or Hour(Now()+6/24)<6,"22-06",IIf([IngateStartDT]<10,"06-10",IIf([IngateStartDT]<19,"10-19","19-22")));

 

[namliam]

namliam,

your help has been amazing. I'm trying to do it the IIF way

I am really going to advice you against the IIF way... and I am not even going to fix it for you....

I left a small little thing I used for testing it... Try and find it yourself.... Hint: You need to replace something with IngateStartDT...

Once again, finaly, I will urge you to use the linked table or function for easy of future maintenance.... Preferably the linked table...

 

[kidrobot]

Namlian, thanks for looking out for me, but this is just a one time report. My boss just wants to compare these times with workers for a quick business decision. Nothing long term! promise! and I really have no clue how to fix that expression lol.

 

[namliam]

iif(IngateStartDT >= 22 or Hour(now()+6/24) < 6, "22-06",
iif(IngateStartDT<10, "06-10",
iif(IngateStartDT<19, "10-19", "19-22")))

If you look at it closely... you may find there is something out of line here....

I will give you another hint: Your query allways returns 22-06 because NOW +6 hours < 6 in your timezone...
i.e. it is later than 18:00 hours for you...

 

[namliam]

You catch me on a good day, try this:

iif(IngateStartDT >= 22 or [B]IngateStartDT[/B] < 6, "22-06",
iif(IngateStartDT<10, "06-10",
iif(IngateStartDT<19, "10-19", "19-22")))

And uhm.... one more thing....

Why did you take out the Hour() function?? You need the hours right???

iif(Hour(IngateStartDT) >= 22 or [B]Hour(IngateStartDT)[/B] < 6, "22-06",
iif(Hour(IngateStartDT)<10, "06-10",
iif(Hour(IngateStartDT)<19, "10-19", "19-22")))

That should do your trick... Please forward all praise to the forum once you hand your boss the report ....