Assistance with Convert Function 1

[Sydney1]

Hi,

I'm trying to change the following Access query field

Assessed: Sum(IIf([FeeType]="A",[FeeTotal],0))

to a field in a SQl View. So far, I have the following

sum(case Convert(money,[feeType]) when 'A' Then [FeeTotal] Else '0') End As Assessed

but am getting an error by the ' and am not sure if I have the converting down correctly. [feetype] is a char field and [feeTotal] is a money.

thanks so much for your help.

Sydney

 

[r937]

sum(case when feeType='A' 
         then convert(money,FeeTotal) 
         else 0 end) as Assessed

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Sydney1]

Hi,

That did it. Thanks so much. I've got one more field that's giving me trouble In Access it is

TFee: IIf([Paid]=0 And [Assessed]>0,[Assessed]+[Waived],IIf([Assessed]+[Paid]<0,0,IIf([Paid]<0 And [Assessed]>0,[Assessed]+[Paid]+[Waived],0)))

And So far I have

Case([Paid] When 0 and [assessed] >0 Then [assessed]+[Waived] when [assessed]+[Waived]<0 Then 0 When [Paid]<0 and [Assessed]>0 then [Assessed]+[Paid]+[Waived] else 0 End) as TFee

and am getting an error at When

Thanks again,

Sydney

 

[r937]

case when paid = 0 and assessed > 0 
     then assessed+waived 
     when assessed+waived < 0 
     then 0 
     when paid < 0 and assessed > 0 
     then assessed+paid+waived 
     else 0 end 
  as tfee

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Sydney1]

Hi,


It appears that SQL doesn't like the column names without the brackets, as when I add it to the same view that names assessed, waived and paid, I get errors for each time assessed, paid, and waived are in the tfee statement.

Thanks again for all your help

Sydney