Using the format function for a percent with four decimal places

[Michelle]

I used the format function to convert text to percentages, because I didn't
get any options in the properties drop-down formatting box in the design view
of my query.

I used the format function in a subquery, then I created another query which
pulled those percentages and I was able to select "4 decimal places" from the
drop-down properties box in this query.

I've created another query that pulls from the second query. I'm using the
IIf function in this one. I did that because the some loans only have one
payment through their lives and others have as many as four payment changes.
If a loan only has one payment during its life, I want the second, third,
fourth, and fifth interest rate fields to say "NONE". If it has more than one
payment, I want it to pull in the percentages for the new payments from my
other query.

My problem is that, once I put in the IIf function, my percentages pull in
like they're supposed to, but they now only have two decimal places and I'm
not able to change the formatting to get my four decimal places back.

Sorry for the long post. Can anyone help me?

Thanks,

Michelle

 

[Clifford Bass]

Hi Michelle,

Skip using the Format() function and try this for the Format property:

#,##0.0000%;;;"NONE"

Note the four parts, divided by semicolons (. The first part is for
positive numbers, the second for negative number, the third for the zero
value and the fourth for null values. When a part is not specified, it will
default to the format defined in the first section.

Clifford Bass

 

[Michelle]

Thank you! That worked perfectly. Is there a way I can do this for currency,
too? If not, it's no big deal, it would just make me easier to add additional
expenses to each payment step.

Michelle

 

[Clifford Bass]

Hi Michelle,

Sure can. Pretty similar:

$#,##0.00;;;"NONE"

Add one or two more zeroes after the decimal if needed. Note that you
can press F1 while you are in the Format property field to get a whole lot of
help on both existing formats and creating your own custom formats.

Clifford Bass

 

[Michelle]

Thanks again! That makes my life so much easier. I had no idea I could do
formatting this way. I'll have check out the Help section to see what else
I'm missing out on.

Michelle

 

[Clifford Bass]

Hi Michelle,

You are welcome!

Clifford Bass