Query to group on Day with sum total on a field & filter on Time but NOT group on the time

[bignose2]

Hi,

I am trying to sum total the two values as below but Grouped per day ONLY, 1 total for the 7th, the 8th etc. etc.
However I need the TmHour to filter the results just between those times
This causes it to no longer be grouped by day but per time hour also.
I just can't work this out, any ideas?
Thanks I/A

table below is with the TmHour ticked to show the format (not as above) for the example.
Unticked it still shows the several results per day.

 

[bob fitz]

Replace the "PeriodDate" column with a calculated field and GroupBY that field. Perhaps something like PDate: Format([PeriodDate], "dd/mm/yyyy")

 

[bignose2]

Hi,

Thank you for your prompt reply

Not helped & can't see why it might?

 

[Pat Hartman]

Change the "Group By" to "Where" for the time column.

NEVER Format a date except for display purposes. The Format() function turns a date into a STRING which makes it stop acting like a date for purposes of sorting and comparing. If you need to extract only the date or time part from a date field, use the DateValue() or TimeValue() functions.

 

[bob fitz]

In light of Pat's advice, which I'm sure will be good advice, Use PDate: DateValue([PeriodDate])

 

[bob fitz]

In Column "TmHour" the Total row should be set to Where rather than GroupBy. That may be all that is required.

 

[bignose2]

Hi,
Very many thanks
"In Column "TmHour" the Total row should be set to Where rather than GroupBy. That may be all that is required."
WORKED
I thought I had tried that & actually most of the other sum functions, just to get a dumb value to avoid the GroupBy but I guess not.
Put it back to the original table value, no format or even datevalue & seems to work fine although will keep eye out for the frequent anomaly with uk & us dates dates getting bent out of shape with day & month reversal.
thanks again.

 

[bob fitz]

Glad you have it working now. Good luck with your project.

 

[Pat Hartman]

"In Column "TmHour" the Total row should be set to Where rather than GroupBy. That may be all that is required."
WORKED

I guess my suggestion to "Change the "Group By" to "Where" for the time column." didn't work But as long as Bob confirmed it, that made it work