Adding 2 field together in a query (including Null) 1

[Kallen]

Hi all,

I am using Access 2000. I am trying to add two fields together in a query. My query looks wrong because it doesn't want to to do the calculation on null values so this is what I have.

The two fields are

1. OneTimeFees
2. OngoingFees

My calculated field will be TotalFees.

Here is what I have thus far.

TotalFees: IIf (IsNull([OneTimeFees])),0,[OneTimeFees]) + IIf (IsNull([Ongoingfees],0,[Ongoingfees]))

Everytime I take my cursor off of the area, I get an error telling me my syntax is wrong. I know what I am doing wrong in general. I believe I am having problems w/ the parenthesese, but I don't know exactly what section. I have added more and taken some out to no avail. Can someone help me figure this out. I would really appreciate it.

Thanks!!

 

[briand2]

Kallen,

This is how I think it can be made to work:

Your current expression is-

TotalFees: IIf (IsNull([OneTimeFees])),0,[OneTimeFees]) + IIf (IsNull([Ongoingfees],0,[Ongoingfees]))

Building it up a bit at a time, I get-

OneTimeFees_part = IIf(IsNull([OneTimeFees]),0,[OneTimeFees])

OngoingFees_part = IIf(IsNull([OngoingFees]),0,[OngoingFees])

TotalFees = OneTimeFees_part + OngoingFees_part

= IIf(IsNull([OneTimeFees]),0,[OneTimeFees]) + IIf(IsNull([OngoingFees]),0,[OngoingFees])

So, to fix your expression, do this:

Step 1: Remove fourth right bracket

Step 2: Add right bracket after "IsNull([Ongoingfees]"

Step 3: Remove last right bracket

Should work!!!

Regards,

Brian

 

[briand2]

Kallen,

By the way, I should have mentioned that you can try using the "NZ" function instead (it converts Null to Zero) thus:

TotalFees = NZ([OneTimeFees]) + NZ([OngoingFees])

Brian

 

[Kallen]

The NZ functions worked like a charm!!!! It was much easier doing it that way.

Thanks! You made my day!!

 

[briand2]

And your thanks have just made my day!!

Cheers,

Brian