Date and Time Grouping Dilemma... 1

[bluenoser337]

In Access 2000, I have a query where I look at the "int(DateTime)" to group records by date. DateTime is in the general date format. This works fine in my Access report. All records are grouped and counted properly. Now I need to query for a report for all records since a certain date AND time. The report no longer grouping properly, I believe because now I can't use the "int" in the query and that means every "DateTime" is different. How can I get around this? Working Query = SELECT Int(ProdTime) AS DateOnly, Color, Length, Width...GROUP BY Int(ProdTime), Color, Length, Width. Non Working Query = SELECT ProdTime, Color, Length, Width...GROUP BY ProdTime, Color, Length, Width. Many Thanks!!!!

 

[dhookom]

You can use DateValue([ProdTime]) to get the date with no time.

Do you understand that the sort order in a query has little or no influence on the order in the report?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[bluenoser337]

Thanks...but I'm not having a problem with getting "only the date"...and it's grouping, not sorting that is causing me grief. I'm a newbie...but my post couldn't have been that vague???? Anyone else have an answer? Thanks again!

 

[cheerio]

Take your original working query and change the source from the base table to a new query on that base table. The new source query can select the records since a particular date and time. Your original query can then group on the int as before.

 

[dhookom]

You state "report no longer grouping properly". Again Do you understand that the sort order in a query has little or no influence on the order in the report?

I suggested using DateValue() in place of the non-conventional Int().

Your use of "=" in "= SELECT Int(ProdTime) AS DateOnly, Color" is very confusing. I expect you actually meant "is Select..." since = is used for controlsource and in code.



Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[bluenoser337]

The query starts with SELECT...not =. The grouping in the query is affecting my report, perhaps because I am looking at summed and counted values in the query. Maybe I should be doing the calculations in each separate report. This is further complicated because I am calling these reports and setting other conditions with a WhereCondition in VB6. Thanks!

 

[dhookom]

Finally I think you have provided enough information. You want to use the date field value in the DoCmd.OpenReport but you want records grouped ignoring the date field.

One possible solution is to create a group header and/or footer for your controls with the Dates in the detail section. Just make the detail section invisible.

There are several other methods of doing this such as a table with a single record and single date field. Use VB6 to set the value of this date field and then add it to your report's record source. Use this field in the criteria of your report's record source.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[bluenoser337]

dhookom...thanks for your patience for someone "old enough to know better". I solved the problem by taking the grouping out of the report "Record Source" SQL. That's where my trouble was happening. Now I basically query everything in the required table with no grouping in the query...and let the report do the math (with the new header as you suggested). I admit that I didn't know how the whole table-query-report thing tied together.