what does this mean?

[kwokv616]

when i want to run a query it says "you tired to execute a query that does not include the specified expression 'Dur1' as part of an aggregate function"
what does this mean?

Dur1 is in the table and it has got valid values...i dont understand what has gone wrong..=.=

 

[pbaldy]

Presumably you have a totals query; each field in the SELECT clause must either be part of an aggregate function or be in the GROUP BY clause. Since you haven't posted the SQL of the query, I can't be more specific than that.

 

[kwokv616]

The SQL is ridiculously long and messy....
it is as follows:

SELECT Rid.Dur1, Rid.Code, Sum(1*IIf([Rid].[layer]=0 And [Rid].

=[Rid].[rcode] And ([rid].[status]="I" Or ([rid].[status]="P" And (Not IsEmpty([Rid].[edate])))),1,0)) AS nopol, Sum([rid].[bsa]*1*IIf([Rid].[code]=[Rid].[rcode] And ([rid].[status]="I" Or ([rid].[status]="P" And (Not IsEmpty([Rid].[edate])))),1,0)) AS bsa, Sum([rid].[bsa]*1*IIf([Rid].[rcode]="SBB" And ([rid].[status]="I" Or ([rid].[status]="P" And (Not IsEmpty([Rid].[edate])))),1,0)) AS sbbbsa, Sum([rid].[bsa]*1*IIf(([Rid].[rcode]="TI" Or [Rid].[rcode]="TI") And ([rid].[status]="I" Or ([rid].[status]="P" And (Not IsEmpty([Rid].[edate])))),1,0)) AS tibsa, Sum(([prem]+[eprem_o]+[eprem_m])*1*IIf([code]=[rcode] And ([status]="I" Or ([status]="P" And Not (IsEmpty([edate])))),1,0)) AS apbas, Sum(([prem]+[eprem_o]+[eprem_m])*1*IIf((Not ([code]=[rcode]) And Not ([rcode] In ("TOPUP","FSB","FSSB","MKB"))) And ([status]="I" Or ([status]="P" And Not (IsEmpty([edate])))),1,0)) AS aprid, Sum([net_prem]*1*IIf(([rcode]="MKB" Or [rcode]=[code]) And ([rid].[status]="I" Or ([rid].[status]="P" And (Not IsEmpty([Rid].[edate])))),1,0)) AS npbas
FROM Rid
WHERE (((Rid.Rcode)<>"DVDPUA") AND ((Rid.Cur)="HKD"))
ORDER BY Rid.Dur1, Rid.Code;

 

[namliam]

You cannot use Sum without a group by query....

If you post queries... please use the code tags (quote my post to see how it works)
and FORMAT your sql... Dont just splash it on the forum

SELECT Rid.Dur1
,      Rid.Code
,      Sum(            1*IIf([Rid].[layer]=0 And [Rid].[code ]=[Rid].[rcode] And ([rid].[status]="I" Or ([rid].[status]="P" And (Not IsEmpty([Rid].[edate])))),1,0)) AS nopol
,      Sum([rid].[bsa]*1*IIf(                    [Rid].[code ]=[Rid].[rcode] And ([rid].[status]="I" Or ([rid].[status]="P" And (Not IsEmpty([Rid].[edate])))),1,0)) AS bsa
,      Sum([rid].[bsa]*1*IIf([Rid].[rcode]="SBB" And ([rid].[status]="I"                             Or ([rid].[status]="P" And (Not IsEmpty([Rid].[edate])))),1,0)) AS sbbbsa
....
FROM Rid
WHERE (((Rid.Rcode)<>"DVDPUA") AND ((Rid.Cur)="HKD"))
Group by <any fields you dont use sum/avg/etc on>
ORDER BY Rid.Dur1, Rid.Code;

Why is there a duplicate OR everywhere?