Calculated field with IIf makes field text rather than percentage

[ChuckW]

Hi,

I am trying to calculate the change in sales between two
months. I have two different tables that I am joining to
form a query. One has CustomerName and Feb05 sales, the
other has CustomerName and Feb04 Sales. The Feb05
represents all customers who bought anything in Feb05
while the Feb04 represents all customers who bought
anything in Feb04. I have joined the tables so that it
includes all records from 2005 and only those that match
in 2004. The problem is that there are some customers
who bought in Feb05 did not buy in Feb04. So if I do a
straight calculation of ChangeFeb05-Feb04)/Feb, I get
error messages for all customers who did not buy in Febo4
which does not look very good on my report. I thought I
had a way around this with the following:

Change: IIf([feb04]=0,"",([Feb05]-[Feb04])/[feb04])

This only calculates a value if there is something in
Feb04. The problem is that it also makes this fields
text in format. I have tried changing this to percentage
both in the query and the report but have been unable to.

How do I create a calculated field that will not give me
errors for people who did not buy in Feb04 yet allows me
to have percentage as the format?

Thanks,

Chuck

 

[MGFoster]

Hi,

I am trying to calculate the change in sales between two
months. I have two different tables that I am joining to
form a query. One has CustomerName and Feb05 sales, the
other has CustomerName and Feb04 Sales. The Feb05
represents all customers who bought anything in Feb05
while the Feb04 represents all customers who bought
anything in Feb04. I have joined the tables so that it
includes all records from 2005 and only those that match
in 2004. The problem is that there are some customers
who bought in Feb05 did not buy in Feb04. So if I do a
straight calculation of ChangeFeb05-Feb04)/Feb, I get
error messages for all customers who did not buy in Febo4
which does not look very good on my report. I thought I
had a way around this with the following:

Change: IIf([feb04]=0,"",([Feb05]-[Feb04])/[feb04])

This only calculates a value if there is something in
Feb04. The problem is that it also makes this fields
text in format. I have tried changing this to percentage
both in the query and the report but have been unable to.

How do I create a calculated field that will not give me
errors for people who did not buy in Feb04 yet allows me
to have percentage as the format?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Change: IIf(IsNull(feb04) or feb04=0,NULL,(Feb05-Feb04)/feb04)

the same result w/ other functions -

Change: (Feb05-Feb04) / IIf(Nz(feb04,0)=0, NULL, feb04)

Using NULL will not change the data type of the column.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiTL7IechKqOuFEgEQJp6QCfSaL4v1AM18neMHxzSEd/Nacc7i8AoPv7
DQtuqvcS7iNYsIu6392m+wJB
=yhg7
-----END PGP SIGNATURE-----