User defined date range and group by (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 07:31
Joined
Dec 5, 2017
Messages
843
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.
Code:
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    85.7 KB · Views: 165

plog

Banishment Pending
Local time
Today, 06:31
Joined
May 11, 2011
Messages
11,597
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:


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:



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

Registered User.
Local time
Today, 07:31
Joined
Dec 5, 2017
Messages
843
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:


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:





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!
 

Users who are viewing this thread

Top Bottom