Multiple queries using one date range (1 Viewer)

[Pat Hartman]

I meant the SQL string. We can't see everything using this view. NoKnocks is using Count()

Press the totals button and that will add an additional line.
NoKnocks should use expression
Op Date should use group by
I can't tell aoubt the third field

 

[AWirsching]

SELECT Count([Operational AAR/Summary]![Investigation Type]) AS NoKnocks, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
WHERE ((([Operational AAR/Summary].[Op Date]) Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]) AND (([Operational AAR/Summary]![Investigation Type])=7));

 

[Pat Hartman]

The third field should be group by

 

[AWirsching]

SELECT Count([Operational AAR/Summary]![Investigation Type]) AS NoKnocks, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
GROUP BY Count([Operational AAR/Summary]![Investigation Type]), [Operational AAR/Summary].[Op Date], [Operational AAR/Summary]![Investigation Type]
HAVING ((([Operational AAR/Summary].[Op Date]) Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]) AND (([Operational AAR/Summary]![Investigation Type])=7));
??

 

[Pat Hartman]

Change the HAVING back to WHERE since it is referencing input fields rather than output fields.

 

[AWirsching]

SELECT Count([Operational AAR/Summary]![Investigation Type]) AS NoKnocks, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
GROUP BY Count([Operational AAR/Summary]![Investigation Type]), [Operational AAR/Summary].[Op Date], [Operational AAR/Summary]![Investigation Type]
Where ((([Operational AAR/Summary].[Op Date]) Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]) AND (([Operational AAR/Summary]![Investigation Type])=7));

 

[Pat Hartman]

The WHERE clause needs to go before the Group by clause.

I thought you were changing the properties on the QBE rather than editing the SQL itself.

 

[CJ_London]

GROUP BY Count([Operational AAR/Summary]![Investigation Type]), [Operational AAR/Summary].[Op Date], [Operational AAR/Summary]![Investigation Type]

just needs to be

GROUP BY [Operational AAR/Summary].[Op Date]

and if you want to group by [Operational AAR/Summary]![Investigation Type] then you need to include it in your select part as well

 

[AWirsching]

The WHERE clause needs to go before the Group by clause.

I thought you were changing the properties on the QBE rather than editing the SQL itself.

First off, what is QBE?
I switched the Where to go before the Group By and all I did was cut and paste but now I have another error message:
SELECT Count([Operational AAR/Summary]![Investigation Type]) AS NoKnocks, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
Where ((([Operational AAR/Summary].[Op Date]) Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]) AND (([Operational AAR/Summary]![Investigation Type])=7));
GROUP BY Count([Operational AAR/Summary]![Investigation Type]), [Operational AAR/Summary].[Op Date], [Operational AAR/Summary]![Investigation Type]

From this query I'm trying to only pick out a specific investigation type (7) as well as by date.

 

[pbaldy]

The QBE is design view as opposed to SQL view. Remove the semi-colon at the end of the WHERE clause and see if that helps.

 

[AWirsching]

Oh ok. Well, that worked with the query. When I went back to the Date Parameters form, I received the following error:

 

[AWirsching]

Oh ok. Well, that worked with the query. When I went back to the Date Parameters form, I received the following error:
View attachment 95651

Apologies, I see what I did! Let me fix this.

 

[AWirsching]

I received the following error when trying the form again after correcting the SQL

This popped up several times (obviously something with the queries), however this time when it brought up the report, several of the answers appeared however they were incorrect.

This is the SQL:

SELECT Count([Operational AAR/Summary]![Investigation Type]) AS NoKnocks, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
WHERE ((([Operational AAR/Summary].[Op Date]) Between Forms![Date Parameters]![Start Date] And Forms![Date Parameters]![End Date]) And (([Operational AAR/Summary]![Investigation Type])=7))
GROUP BY [Operational AAR/Summary].[Op Date];

 

[pbaldy]

If [Operational AAR/Summary] is a query, does it run without error? If not, and it doesn't have an Expr1 field, keep drilling down until you find the offending query. Or can you attach the db here?

 

[AWirsching]

[Operational AAR/Summary] is the main table. I was able to get everything running until I had to add the date parameter....
Upload file is too large. =(

 

[pbaldy]

Try doing a compact/repair and then zipping it. Or just attach a db with that table.

 

[pbaldy]

The query above runs fine. The error comes from the item 10 subform:

SELECT Count([Operational AAR/Summary]![Person(s)/Animal(s) Injured/Killed]) AS InjuredKilled, [Operational AAR/Summary]![Person(s)/Animal(s) Injured/Killed] AS Expr1, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
WHERE ((([Operational AAR/Summary]![Person(s)/Animal(s) Injured/Killed])=2) And (([Operational AAR/Summary].[Op Date]) Between Forms![Date Parameters]![Start Date] And Forms![Date Parameters]![End Date]))
GROUP BY [Operational AAR/Summary].[Op Date];

The field in Expr1 needs to be in the Group By clause, or have an aggregate function applied to it.

 

[pbaldy]

Or deleted, since I just noticed it's the same as the first field.

 

[AWirsching]

I deleted it. Same error appears this time only once. Again the answers provided are wrong.

 

[pbaldy]

I assume you have the same thing happening in another subreport. I'm on the phone so can't check right now.