Override a Value

[AndyShuter]

In the design grid of my query I have a calculation that possibly could return a minus figure.

What I would like to do is set a condition that would automatically return the value as zero, should the result be negative. I know I can do this in code, but need to do it on the design grid of the query - is this possible?

My calc is

CapOffRedFactor: ([Rate]-[CapOffRate])*([Term]/12)

Many Thanks in Advance

 

[WayneRyan]

Andy,

CapOffRedFactor: IIf(([Rate]-[CapOffRate])*([Term]/12) < 0, 0, ([Rate]-[CapOffRate])*([Term]/12))

Wayne

 

[AndyShuter]

Thanks mate!

This will be a very useful function to me!!!

 

[WayneRyan]

Andy,

No problem, the IIf is very useful. However, since you can nest them,
they can be pretty unintelligible. If you get one that gets too complex
you can consider using a VBA function instead.

Wayne

 

[AndyShuter]

Sorry to be a pain in the ass!

Can you help me with this one?

5yrComm: ([Advance]*((([Rate]-[TrueBR])*[DIC])/100)*([5yrTerm]/12))/100

I would like to set this to zero if Check56 is False!

Thanks Again Mate

 

[WayneRyan]

Andy,

5yrComm: IIf([Check56], ([Advance]*((([Rate]-[TrueBR])*[DIC])/100)*([5yrTerm]/12))/100, 0)

Wayne

 

[WayneRyan]

Andy,

Just a syntax example of a nested IIf.

I indent them when building to keep things organized. In a query,
it is all strung together.

IIf(Field1 = "1", "Value = 1",
    IIf(Field1 = "2", "Value = 2",
        IIf(Field1 = "3", "Value = 3", "Value is BIG")

hth,
Wayne

 

[AndyShuter]

Can I have just one more....then I'll leave u in peace!!

TrueBR: [BaseRate]+[SchUplift]+[5yrUplift]

If 5yr uplift is <49 then 5yr uplift is zero?

I'll swot up on this function, just had a long day and the brain is about to explode!!!

Thanks

Andy

 

[WayneRyan]

Andy,

TrueBR: IIf([5yrUplift] < 49, [BaseRate]+[SchUplift], [BaseRate]+[SchUplift]+[5yrUplift])

p.s. Missed closing right parens in last post ... oh well.

Wayne