cannot open report ...

[Guest]

Some help please. I am trying to create a report from the query below. The
query returns the correct results, however I am getting the following message
when I try to create the report via the report wizard "Multi-leveL GROUP BY
clause is not allowed in a subquery" and the report only opens in design view

SELECT log.Date, log.ObjectName, log.[Old Value], log.[New Value], log.[No],
(SELECT MAX([Date/Time])
FROM log AS L1
WHERE L1.[ObjectName] = log.[ObjectName] AND L1.[Date/Time] <
log.[Date/Time]) AS Previous, log.[Date/Time],
DateDiff("s",[Previous],[Date/Time]) AS Seconds
FROM log
WHERE (((DateValue([log].[date]))=[enter date]))
ORDER BY log.ObjectName, log.[Date/Time];

The query group suggested that I post to the reports group. Any suggestions

 

[Marshall Barton]

Some help please. I am trying to create a report from the query below. The
query returns the correct results, however I am getting the following message
when I try to create the report via the report wizard "Multi-leveL GROUP BY
clause is not allowed in a subquery" and the report only opens in design view

SELECT log.Date, log.ObjectName, log.[Old Value], log.[New Value], log.[No],
(SELECT MAX([Date/Time])
FROM log AS L1
WHERE L1.[ObjectName] = log.[ObjectName] AND L1.[Date/Time] <
log.[Date/Time]) AS Previous, log.[Date/Time],
DateDiff("s",[Previous],[Date/Time]) AS Seconds
FROM log
WHERE (((DateValue([log].[date]))=[enter date]))
ORDER BY log.ObjectName, log.[Date/Time];

The query group suggested that I post to the reports group. Any suggestions

That happens when your report has something in Sorting and
Grouping or a text box expression that uses an aggregate
function AND you are using a subquery in its record source
query.

The usual workaround is to cahnge the subquery to the
equivalent Domain Aggregate function (in this case DMax).