iif function in query

[Guest]

I have an iif function in a query as follows:
Gas Jan 06-05: IIf([Gas Jan 2005]<1,"",IIf([Gas Jan 2006]<1,"",([Gas Jan
2006]-[gas Jan 2005])/[Gas Jan 2005]))

It appears to be calculating properly but I am not able to format the field
as a percent. When I go into field properties and click on the down arrow
for the format box it is blank. Even if I manually type in the Percent
format, it still doesn't change the exponential format the info is coming out
in. Before I added the iif (which I did so if there were zero vol in either
2005 or 2006 it wouldn't try to do the math) I did get the result format the
way I wanted.

 

[Guest]

h iskigal,

You can try:

format(IIf([Gas Jan 2005]<1,"",IIf([Gas Jan 2006]<1,"",([Gas Jan
2006]-[gas Jan 2005])/[Gas Jan 2005])), "percent")

 

[John Spencer]

SInce you are forcing zero-length strings, Access is casting all the
returned values as strings. Try returning NULL instead of ""

Gas Jan 06-05: IIf([Gas Jan 2005]<1,NULL,IIf([Gas Jan 2006]<1,NULL
,([Gas Jan 2006]-[gas Jan 2005])/[Gas Jan 2005]))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..