|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 8th, 2009, 08:01 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
IIF statement in text box
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? 
|
|
#2
January 8th, 2009, 08:22 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
IIF statement in text box
On Thu, 8 Jan 2009 12:01:07 -0800, Jeff H wrote:
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? =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 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#3
January 9th, 2009, 02:16 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
IIF statement in text box
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" wrote: On Thu, 8 Jan 2009 12:01:07 -0800, Jeff H wrote: 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? =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 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#4
January 9th, 2009, 03:08 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
IIF statement in text box
On Thu, 8 Jan 2009 18:16:02 -0800, Jeff H wrote:
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" wrote: On Thu, 8 Jan 2009 12:01:07 -0800, Jeff H wrote: 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? =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 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail 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.")) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
