Access Query Error

[ravininave]

This is my Query which I am running from Visual Basic 6 but count function returning wrong count

Rs.Open "SELECT 1 as rowno, AgentCode, AgentName, TotalAg, Amount,MainAmt, DiscAmt,CommAmt,TdAmt, NewTDSAmt, CommAmt - " & (TdAmt + NewTDSAmt) & " as lAmt, Commission FROM (" _
& "Select AgentCode, AgentName, TotalAg, Amount,MainAmt, DiscAmt,CommAmt,CommAmt * " & TDSAmtFromMast & " / 100 as TdAmt, CommAmt * " & NewTDSAmt & "/ 100 as NewTDSAmt, Commission from (" _
& "Select Comms.AgentCode, AgentName, Count(Registration.AgentCode) as TotalAg, sum(Amount) as Amount,sum(MainAmt) as MainAmt, sum(DiscAmt) as DiscAmt,sum(CommAmt) as CommAmt, AgentMast.Commission from (Comms INNER JOIN AgentMast on Comms.AgentCode = AgentMast.AgentCode ) " _
& "LEFT JOIN JOIN Registration ON (Comms.CodeNo = Registration.CodeNo and PurchDate >=#" & CDate(FDate.Value) & "# and Purchdate <=#" & CDate(TDate.Value) & "# and Registration.AgentCode =Comms.AgentCode) WHERE ((RecDate >=#" & CDate(FDate.Value) & "# and RecDate <=#" & CDate(TDate.Value) & "# and Comms.AgentCode <>0 )) AND ((isnull(RelDate) = TRUE and (RelTag =0 or Isnull(RelTag = TRUE)))) OR (ReLDate >=#" & CDate(FDate.Value) & "# and ReLDate <=#" & CDate(TDate.Value) & "# and Comms.AgentCode <>0) Group By Comms.AgentCode, AgentMast.AgentName, AgentMast.Commission ) Test) BEST", DBConn, adOpenForwardOnly, adLockOptimistic

[CJ_London]

in what way is it wrong?

And I don't see a count function

Suggest also tidy your code up so it is readable, seems to be a load of virtual tables

[mike60smart]

Hi

If you can upload a zipped copy of the database it would make it easier to help.