User defined date range and group by

[Zydeceltico]

Hi All,

I'm trying to design a query to run on a command button (cmdGetResults) on a form after a user has selected two dates: each from a different control (i.e,, txtDateTime1 and txtDateTime2).

Once the two dates are selected, I'd like to click cmdGetResults and have a report open with the results grouped by Date and then job number. The query will get more involved after this but I'm pretty sure I can figure the rest of it out. I've been searching here and google and am just getting more confused.

I keep getting an "you didn't aggregate" message.

I've attached a screen shot of the current returned datasheet and here's the current SQL.

SELECT tblinspectionevent.datetime,
       tbljobs.jobnumber,
       lutblinspectiontypes.inspectiontype
FROM   lutblinspectiontypes
       INNER JOIN (tbljobs
                   INNER JOIN tblinspectionevent
                           ON tbljobs.job_id = tblinspectionevent.job_fk)
               ON lutblinspectiontypes.inspectiontype_id =
                  tblinspectionevent.inspectiontype_fk;

BTW - I'm writing this post because I am foreseeing issues with the DateTime field even before I start trying to work through this.

First things first though: Number one question: How do I pass the two user-selected dates from controls on the form to the SQL statement using "BETWEEN" so that the user defines an exact range?

Also, as you can see in the pic, I store Date and Time in the same field. When and inspection is performed that field defaults =Now(). When I get to the report stage I would like to group by Date only and then have line items by Time and JobNumber. What should I be considering to be able to do this?

Thanks1

Tim

 

[plog]

Since this will be displayed in a report, I would not use any criteria on the query. Keep it as it is, build a report using it and then use the Docmd.OpenReport to open and filter the report:

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

You build a criteria string using the user input and then open the report using that string. Then for just the date and just the time, use DateValue and TimeValue functions in your query:

MS Access: DateValue Function

This MSAccess tutorial explains how to use the Access DateValue function with syntax and examples. The Microsoft Access DateValue function converts a string to a date.

www.techonthenet.com

MS Access: TimeValue Function

This MSAccess tutorial explains how to use the Access TimeValue function with syntax and examples. The Microsoft Access TimeValue function converts a string to a time.

www.techonthenet.com

with the results grouped by Date and then job number

GROUP BY occurs simutaneously on all fields you've selected to GROUP BY, so the 'then' in your statement makes no sense. You can ORDER BY fields in which case sequence of the fields does matter, but not with GROUP BY

 

[Zydeceltico]

Since this will be displayed in a report, I would not use any criteria on the query. Keep it as it is, build a report using it and then use the Docmd.OpenReport to open and filter the report:

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

You build a criteria string using the user input and then open the report using that string. Then for just the date and just the time, use DateValue and TimeValue functions in your query:

MS Access: DateValue Function

This MSAccess tutorial explains how to use the Access DateValue function with syntax and examples. The Microsoft Access DateValue function converts a string to a date.

www.techonthenet.com

MS Access: TimeValue Function

This MSAccess tutorial explains how to use the Access TimeValue function with syntax and examples. The Microsoft Access TimeValue function converts a string to a time.

www.techonthenet.com

GROUP BY occurs simutaneously on all fields you've selected to GROUP BY, so the 'then' in your statement makes no sense. You can ORDER BY fields in which case sequence of the fields does matter, but not with GROUP BY

That's all very helpful plog. That's the overview i was hoping for. I'll give it a go. Thanks!