An expression using 2 fields but returning the most recent value 1

[istuart]

In Access97, I have a table containing two fields "Rate" and RenewalRate". I created a query and would like it to return the most recent value of the two. Using "Rate" as the primary field, If "RenewalRate" has no value then return the value of "Rate". I used the following expression:

CurrentRate: IIf(IsNull([RenewalRate]),"",[Rate])

...But I got the wrong result.

Please help.

Thanks.

 

[ETID]

Try expressing it this way....

CurrentRate: IIf([RenewalRate]=null,"",[Rate])

 

[istuart]

Thanks so much for your quick response to my query woe...but, the results were the same. Actually, I had to change it to IIF([Rate]=null,"",[RenewalRate]) to give me at least part of the result.

Maybe my explanation was too muddled. Please see an example of what the query or report results should be below:

Rate(field1) RenewalRate(field2) CurrentRate(Expr1)
$600 $600
$800 $860 $860
$900 $900
$750 $775 $775

The expression that I built returns the value of the Rate field if there is no value in the RenewalRate field, but does not return the value in the RenewalRate field (it leaves it empty) as follows:

Ratefield1) RenewalRate(field2) CurrentRate(Expr1)
$600
$800 $860 $860
$900
$750 $775 $775

I'm trying to do this in the query so that I can include an "Average of Current Rates" in the report summary.

Maybe there's a better way of doing this?

Thanks again.

 

[Dreamboat]

IIF([RenewalRate]=null,[Rate],[RenewalRate])

Because you don't really want CurrentRate to ever say "", right?

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[istuart]

YES....YES...YES!!! That's exactly right and the logic is so clear in the statement. Thank you!

 

[Dreamboat]

You bet. And don't worry, it took me a while to figure it out, too, duh!!

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com