Conditional SUM and SUM calculated field

[Farzam]

Dear all,

I have SQL back and Access Project ADP as front
the problem is infact with ADP ... i have been using Acess and VBA for long , when i was using MDB simple access database sumif and or conditional sum like =sum(iif(A=9,amount))
they worked perfectly alright,
but in ADP report i can not use SUMIF or conditional sum or even i can not use simple SUM function to calculated a previously calculated filed....
i can not get them from a base table or query becoz my report is fed up with entries from a form with filtered criterias....i mean the report is based on a filtered recordset from a form....
so now i want to calculated or sum the amount first a simple sum like
TOTAL Amount = sum(amount)
conditional TOTALs like
Ongoing Amount = sum(iif([Contract_type]="p",[amount],0))

they doesnot work
I wanted to use the method through VBA but dont know if i can
so can you find a solution for this ...i will be so thankful to you all

thanks
waiting

 

[lameid]

Anything you could do previously in a form or report with a MDB backend, you should still be able to do.

What exactly do you mean by they do not work?

 

[Farzam]

that is what i am saying it should work as far as it is all access environment,

but as the function IF and IIF with double I is different i think the engine of access differs,

they dont work " I mean the function SUM(IIF()) and other conditional aggregate functions do not work
if you have SQL SERVER connect an ADP and try to use this formula or function at the form footer or report footer ...
=SUM(IIF([city]="Paris",[Amount],0))
this is a correct formula working in MDB but not in ADP access project ,
also we can not sum a simple calculated field ,
like I have
Text1= [Figure1]+[Figure2]

=Sum([Text1])
it will return nothing ....
i dont know why but on the forums i read that you can not SUM calculated fields, as TEXT1 is a calculated field

regards,

 

[lameid]

You can only use aggregate functions on things you can do to the data... so you can't take a sum of calculated controls... You could add the elements together and sum that or take the running sum of the field.

I'm surprised it bombs on IIF but since they completely remove Jet I guess that makes sense as SQL server does not have IIF (it does however have a Case statement similar in function to VBA's select case). Instead you can build the field in you SQL statement / record source...

Select Case When [City] = 'Paris' Then [Amount] Else 0 End As Paris Amount
From Table

You can add as many when-then cases to a case statement as you like. This is not the common use of the syntax for a SQL CASE statment. For the more common usage check out Books Online (it is intended to be provide the same functionality as the VBA select case statement).

 

[lameid]

Also worth mentioning is there is a forum for ADP's...


forum958