Two nearly identical queries one saves the other won't and returns "'AggregateType' is not..."

[PCartland]

I have two queries created using Builder in Design View. The only difference between the two queries is that one evaluates an expression and returns records where the value of the expression is <11 and the other when the values are >10. In SQL view the two queries are identical except for the <11/>10. In design view the column with the expression that's evaluated for the where clause has "Expr1:" in front of the expression for the query that won't save and doesn't have anything in front of it in the query that does save.

I've tried deleting the "Expr1:" but Design view just reinserts it.

The SQL View for the one that saves is:

INSERT INTO MonthlyMetric ( MetricYear, [Metric Month],MetricType, MetricEndDate, Metric, MetricValue )
SELECT IIf(DatePart("q",[Forms]![MonthlyAppend]![End Date])=4,DatePart("yyyy",[Forms]![Monthly Append]![EndDate])+1,DatePart("yyyy",[Forms]![Monthly Append]![End Date])) AS[Metric Year], Month([Forms]![Monthly Append]![End Date]) AS [Metric Month],"Count of CRs Over Decision Goal by Priority" AS [Metric Type], [Forms]![MonthlyAppend]![End Date] AS Expr4, [Change Request Intake].[Priority Level],Count([Change Request Intake].[Change Request #]) AS [CountOfChange Request #]
FROM [Change Request Intake]
WHERE ((([Change RequestIntake].Decision)<>"Cancelled") AND (([Change Request Intake].[ForAdmin Only]) Is Null) AND (([Change Request Intake].[Date Ready ForAssessment])<[Forms]![Monthly Append]![End Date]) AND (([Change RequestIntake].[Decision Final Date]) Is Null Or ([Change Request Intake].[DecisionFinal Date])>DateAdd('m',-1,[Forms]![Monthly Append]![End Date])) AND((IIf(IsNull([Change Request Intake]![Decision FinalDate]),DateDiff('d',[Change Request Intake]![Date Ready ForAssessment],[Forms]![Monthly Append]![End Date]),DateDiff('d',[Change RequestIntake]![Date Ready For Assessment],[Change Request Intake]![Decision FinalDate])))>10))
GROUP BY [Change Request Intake].[Priority Level]
HAVING ((([Change Request Intake].[PriorityLevel])="Emergency" Or ([Change Request Intake].[PriorityLevel])="Level 1"));

The one that won't save is:

INSERT INTO MonthlyMetric ( MetricYear, [Metric Month],MetricType, MetricEndDate, Metric, MetricValue )
SELECT IIf(DatePart("q",[Forms]![MonthlyAppend]![End Date])=4,DatePart("yyyy",[Forms]![Monthly Append]![EndDate])+1,DatePart("yyyy",[Forms]![Monthly Append]![End Date])) AS[Metric Year], Month([Forms]![Monthly Append]![End Date]) AS [Metric Month], "Countof CRs Under Decision Goal by Priority" AS [Metric Type], [Forms]![MonthlyAppend]![End Date] AS Expr4, [Change Request Intake].[Priority Level],Count([Change Request Intake].[Change Request #]) AS [CountOfChange Request #]
FROM [Change Request Intake]
WHERE ((([Change RequestIntake].Decision)<>"Cancelled") AND (([Change RequestIntake].[For Admin Only]) Is Null) AND (([Change Request Intake].[Date ReadyFor Assessment])<[Forms]![Monthly Append]![End Date]) AND (([Change RequestIntake].[Decision Final Date]) Is Null Or ([Change Request Intake].[DecisionFinal Date])>DateAdd('m',-1,[Forms]![Monthly Append]![End Date])) AND((IIf(IsNull([Change Request Intake]![Decision FinalDate]),DateDiff('d',[Change Request Intake]![Date Ready ForAssessment],[Forms]![Monthly Append]![End Date]),DateDiff('d',[Change RequestIntake]![Date Ready For Assessment],[Change Request Intake]![Decision FinalDate])))<11))
GROUP BY [Change Request Intake].[Priority Level]
HAVING ((([Change Request Intake].[PriorityLevel])="Emergency" Or ([Change Request Intake].[PriorityLevel])="Level 1"));

Both queries run but I need to save the one that doesn't save.
Any suggestions?


Thanks

[AccessPower]

I ran a difference to see if there were any errors. Here's a picture of differences:



The one on the left is the one that saves.

[AccessPower]

Another option:

Take the one that saves in the navigation pane.
Right click it> Copy.
Right click below it> Paste.
Change the name to your desired name.
Edit in SQL view to make the minor changes. Make one change at a time. So change the Over to Under. Save. If that works, change the >10 to <11. Save

[PCartland]

Thanks, I've been trying the cut and paste and gotten nowhere. What tool did you use to run the difference?

Thanks again

[Bulzie]

Change the one that works to <11 (don't save it just run the select part) and see if it works. If it does, do a SaveAs and save it as your 2nd query.

[AccessPower]

https://www.diffchecker.com/diff

I use this quite a bit to spot steganography. This is actually the first time I've used it with code. Works well.

[AccessPower]

Change the one that works to <11 (don't save it just run the select part) and see if it works. If it does, do a SaveAs and save it as your 2nd query.

And this would be the short way around. haha! Very nice Bulzie

[PCartland]

That is how I created the second query in the first place. I just saved the query that worked as a copy of. It saved. I changed >10 to <11 in design view and it won't save. I get the same error message.

[AccessPower]

Is there any way you can post a copy of the database?

Compact and repair / Zip it before you upload.

[AccessPower]

If not, try doing your edits in SQL view. It could be that the query is getting altered in design view.

[PCartland]

Unfortunately I can't. I'll try that.

[Bulzie]

Maybe the results returned are null for the <11 so giving error when trying to insert. Open the query that works (the >10 one) in design view, change the query to a Select query and take off the Totals(Groupby) button and run it, does it return data? Now change the criteria to <11, does it return records?

[AccessPower]

It's odd that it isn't letting hims save, though. It should at least let the save operation happen.

Are you getting any errors?

[PCartland]

I'm not sure what happened but the last time I tried the copy and paste it worked. I hate resolutions where I can point to what I fixed.

Thanks

[AccessPower]

You're welcome. Glad we got it working. It was probably just a piece of formatting that got lost in the copy-paste process.