Remove MsgBox created from a Query (1 Viewer)

[RaWiKe]

Here is the SQL from my Query

SELECT PreStatus.Operator, PreStatus.Mode, Sum(PreStatus.CountOfMode) AS SumOfCountOfMode, IIf([SumOfCountOfMode]>=3,"YES","NO") AS [Current], PreStatus.Rate, PreStatus.Last, PreStatus.First, PreStatus.DET
FROM PreStatus
GROUP BY PreStatus.Operator, PreStatus.Mode, IIf([SumOfCountOfMode]>=3,"YES","NO"), PreStatus.Rate, PreStatus.Last, PreStatus.First, PreStatus.DET
HAVING (((PreStatus.Rate) Like "*" & [Forms]![Status]![Rate] & "*") AND ((PreStatus.Last) Like "*" & [Forms]![Status]![Last] & "*") AND ((PreStatus.First) Like "*" & [Forms]![Status]![First] & "*") AND ((PreStatus.DET) Like "*" & [Forms]![Status]![DET] & "*"))
ORDER BY PreStatus.Operator;

When I run Query a dialog box request for SumOfCountOfMode appears. Is there anyway I can turn this MsgBox off?

This Database is used for the user to find out if their personel are current in said data. The user will be running this query through a Form and will get their results via a report if they put anything in this MsgBox it could possibly skew the data they are looking for.

 

[Pat Hartman]

The syntax is incorrect. You can't reference the name of a calculated field in group by.

Try saving the totals query and then create another query based on it to do this group by.

 

[RaWiKe]

Thank You this worked I was trying not to have to make another query but I guess that is what I needed to do. I had to make 4 querys just to get that simple task done:banghead:

 

[Pat Hartman]

SQL is a simplistic language. Sometimes you need to coerce it to get it to do what you need. Nested queries are a simple and common solution to control how the query engine parses a query.