IIF statement in text box

  • Thread starter Thread starter Jeff H
  • Start date Start date
J

Jeff H

I am trying to create an IIf statement that will generate the sentence "Your
account balance is $XX.XXX" for customers who have a balance due. Balance
is a field in my query.

I have put in the statement =IIf([Balance]>0.01,"As of the end of January,
your account balance was $" & [Balance] ,"") For customers with an even
dollar balance, the amount prints as $XX. Is there a way to format this
field so it will appear as $XX.XX even if the amount were, say $50.00?
 
I am trying to create an IIf statement that will generate the sentence "Your
account balance is $XX.XXX" for customers who have a balance due. Balance
is a field in my query.

I have put in the statement =IIf([Balance]>0.01,"As of the end of January,
your account balance was $" & [Balance] ,"") For customers with an even
dollar balance, the amount prints as $XX. Is there a way to format this
field so it will appear as $XX.XX even if the amount were, say $50.00?
Click to expand...

=IIf([Balance]>0.01,"As of the end of January, your account balance
was " & Format([Balance],"$#,##0.00") ,"")

In addition to always showing 2 decimals, balances of less than $1
will show the 0 in the dollar position.
For example a balance of .99 will show as $0.99

Now what will you do if the balance is exactly .01, or even a credit
balance, i.e. -25.35?



E
 
Thank you - I think that should do what I need. Do you know if there are any
version issues - I'm using 2003.

The statement really should begin IIF[balance]>=.01.... This text only
applies to customers who owe money. There is an alternate text for credit
balances, which in the report I am trying to redesign was in a different text
box - I may combine them. The paid in full folks are covered by a totally
different part of the report.


fredg said:
I am trying to create an IIf statement that will generate the sentence "Your
account balance is $XX.XXX" for customers who have a balance due. Balance
is a field in my query.

I have put in the statement =IIf([Balance]>0.01,"As of the end of January,
your account balance was $" & [Balance] ,"") For customers with an even
dollar balance, the amount prints as $XX. Is there a way to format this
field so it will appear as $XX.XX even if the amount were, say $50.00?
Click to expand...

=IIf([Balance]>0.01,"As of the end of January, your account balance
was " & Format([Balance],"$#,##0.00") ,"")

In addition to always showing 2 decimals, balances of less than $1
will show the 0 in the dollar position.
For example a balance of .99 will show as $0.99

Now what will you do if the balance is exactly .01, or even a credit
balance, i.e. -25.35?



E
Click to expand...
 
Thank you - I think that should do what I need. Do you know if there are any
version issues - I'm using 2003.

The statement really should begin IIF[balance]>=.01.... This text only
applies to customers who owe money. There is an alternate text for credit
balances, which in the report I am trying to redesign was in a different text
box - I may combine them. The paid in full folks are covered by a totally
different part of the report.

fredg said:
I am trying to create an IIf statement that will generate the sentence "Your
account balance is $XX.XXX" for customers who have a balance due. Balance
is a field in my query.

I have put in the statement =IIf([Balance]>0.01,"As of the end of January,
your account balance was $" & [Balance] ,"") For customers with an even
dollar balance, the amount prints as $XX. Is there a way to format this
field so it will appear as $XX.XX even if the amount were, say $50.00?
Click to expand...

=IIf([Balance]>0.01,"As of the end of January, your account balance
was " & Format([Balance],"$#,##0.00") ,"")

In addition to always showing 2 decimals, balances of less than $1
will show the 0 in the dollar position.
For example a balance of .99 will show as $0.99

Now what will you do if the balance is exactly .01, or even a credit
balance, i.e. -25.35?

E
Click to expand...
Click to expand...

No version differences.
You could combine the 3 possible situations in one control.

=IIf([Balance]>0,"As of the end of January, your account balance was "
& Format([Balance],"$#,##0.00"),IIf([Balance]<0,"As of the end of
January your account had a credit balance of " &
Format([Balance],"$#,##0.00") ,"As of the end of January your account
is paid in full."))
 
Back
Top