424,506 Members | 2,252 Online
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

# Multiple Number formats

 P: 38 I'm working on a database for an Insurance Broker. A couple of the field columns in the benefits table are either \$ or %, based on the company (co pay 50% or \$10). Is there a way that I can display these numbers in the correct format on the form? If <=1, then percent format, If >1, Currency format. If not, can you think of another way to pull this data without using text? I need these numbers in calculations. Thanks in advance. Kelly (still learning daily!) Aug 12 '08 #1
21 Replies

 100+ P: 167 Hi Kelly, I don't see how could you use these numbers at the same formula even if you could pul this out. Maybe you have an example of the formula that would do the calculations with this data? I believe it would be better to have two fields Number and Percent, and than you can use Nz() to make the calculations with them. Or maybe you could make some kind of 'payment type' combo box giving the choice to select Fixed amount or Percent. This would tell the program which formula to use e.g. =iif(PaymentType="Fixed";[Charge]+[Amount];[Charge]*(1+[Percent])) Aug 12 '08 #2

 P: 38 The calculations will work based on the number, I just need to "show" the numbers in the correct format. I tried this: Expand|Select|Wrap|Line Numbers If Me.DedSingPDIn <= 1 Then     Me.dedsinPDIn = Format(Me.DedSingPDIn, "0.00%")     Else     Me.DedSingPDIn = Format(Me.DedSingPDIn, "\$#,###.00") End If in the OnCurrent Event of the form, but it doesn't seem to work. Any ideas? Thanks, Kelly Aug 12 '08 #3

 100+ P: 167 The calculations will work based on the number, I just need to "show" the numbers in the correct format. I tried this: If Me.DedSingPDIn <= 1 Then Me.dedsinPDIn = Format(Me.DedSingPDIn, "0.00%") Else Me.DedSingPDIn = Format(Me.DedSingPDIn, "\$#,###.00") End If in the OnCurrent Event of the form, but it doesn't seem to work. Any ideas? Thanks, Kelly I got it in query: [HTML]I presume you have one field (Number1) and if the value is less than 1 than it's %, else it's Standard number. VarFormat: IIf([Number1]<1;Format([Number1];"Percent");Format([Number1];"Standard"))[/HTML] The code you used seems to be OK, but you need to have a Function for it. If you want to make calculations in your code than create a Public function and use the code you used in On current event. Aug 13 '08 #4

 Expert Mod 15k+ P: 31,186 Look into Conditional Formatting. It would seem perfect for this problem. Welcome to Bytes! Aug 13 '08 #5

 P: 38 I got it in query: [HTML]I presume you have one field (Number1) and if the value is less than 1 than it's %, else it's Standard number. VarFormat: IIf([Number1]<1;Format([Number1];"Percent");Format([Number1];"Standard"))[/HTML] The code you used seems to be OK, but you need to have a Function for it. If you want to make calculations in your code than create a Public function and use the code you used in On current event. This is exactly what I'm looking for. In the query, how do I use this function? Do I type it exactly the way you did in the field name? It doesn't seem to be working for me. Sorry, I'm a bit new at this. I've been working in Access for a long time, but this problem seems to be getting the best of me! Kelly Aug 14 '08 #6

 100+ P: 167 This is exactly what I'm looking for. In the query, how do I use this function? Do I type it exactly the way you did in the field name? It doesn't seem to be working for me. Sorry, I'm a bit new at this. I've been working in Access for a long time, but this problem seems to be getting the best of me! Kelly [HTML]You need to put your own field name in the place of "Number1" "VarFormat" is a name of the new field I created. Function starts at: "IIf.... Depending on your regional settings you might need to replace semicolon ";" with comma "," in the IIF function.[/HTML] Also, as NeoPa suggested above, you might use conditional formatting for this. Try reading help in Access about Conditional formatting. Aug 14 '08 #7

 Expert Mod 15k+ P: 31,186 Kelly, bear in mind, that approach returns a string value rather than a numeric which is simply DISPLAYED in a particular way. Aug 14 '08 #8

 100+ P: 167 Is there a way that I can display these numbers in the correct format on the form? As she said in her original post she is using "numbers" (I presume this field is formatted as Number), so Format function should be OK. Aug 14 '08 #9

 Expert Mod 15k+ P: 31,186 In post #6 Kelly refers to returning the value in a query. If this query is used for the form and the Format() function is used in the query, then the value the form gets will be a string. Aug 14 '08 #10

 Expert 2.5K+ P: 3,532 Hey folks! If you look at the line # 2 of the code the OP posted Expand|Select|Wrap|Line Numbers If Me.DedSingPDIn <= 1 Then     Me.dedsinPDIn = Format(Me.DedSingPDIn, "0.00%")     Else     Me.DedSingPDIn = Format(Me.DedSingPDIn, "\$#,###.00") End If you may notice that the control name doesn't appear the same way as it does elsewhere in the code. Access didn't capitalize the first "d" or the "s." That's because the control name is misspelled! If you replace Me.dedsinPDIn with Me.DedSingPDIn his code works just fine! And strings that can be interpreted as numbers can be used in calculations by using the Val() function. Welcome to Bytes! Linq ;0)> Aug 14 '08 #11

 Expert Mod 15k+ P: 31,186 This is exactly what I'm looking for. In the query, how do I use this function? ... Good spot Linq, but it was not the post I was referring to (nor the original question). My response was to the idea of formulating a query to provide the data for the form (post #6), and I believe my point holds true. Certainly it's possible to provide the data as a string, then convert it back to a number, but I can't see why that would be recommended. It would just leave the fundamental problem unanswered in my view (although a practical work-around). Aug 14 '08 #12

 P: 38 Okay, the query works well until I get to the second part of the IIF statement. The "standard" formatting leaves "blanks" in the query. I really appreciate all the help! Thank you so much! Kelly Aug 14 '08 #13

 P: 38 Look into Conditional Formatting. It would seem perfect for this problem. Welcome to Bytes! I tried conditional formatting (which to me seemed like the perfect way to fix this problem), but it only works with Bold/Underline and colors, unless there is something I am missing? It would be nice if it worked with number formats. My problem would be solved! :) Aug 14 '08 #14

 100+ P: 167 ------ Okay, the query works well until I get to the second part of the IIF statement. The "standard" formatting leaves "blanks" in the query. I really appreciate all the help! Thank you so much! Kelly In the table that contains original "Number1" for that field try selecting: [HTML]Data type: Number Field size: Double Format: Standard[/HTML] Aug 14 '08 #15

 P: 38 In the table that contains original "Number1" for that field try selecting: [HTML]Data type: Number Field size: Double Format: Standard[/HTML] Still doesn't work. Here's what I typed: Phy Office Visit In: IIf([phys office visit in]<=1,Format([phys office visit in],"Percent",Format([phys office visit in],"Standard"))) Did I miss something? I feel like I am SO CLOSE!! :) I'd really like to have the other format as "currency"...tried that and it doesn't work either. Aug 14 '08 #16

 100+ P: 167 Still doesn't work. Here's what I typed: Phy Office Visit In: IIf([phys office visit in]<=1,Format([phys office visit in],"Percent",Format([phys office visit in],"Standard"))) Did I miss something? I feel like I am SO CLOSE!! :) I'd really like to have the other format as "currency"...tried that and it doesn't work either. Originally you had 2 closing parenthesis ")" at the end of the second Format function, and nothing at the end of first. Try this: Expand|Select|Wrap|Line Numbers Phy Office Visit In: IIf([phys office visit in]<=1,Format([phys office visit in],"Percent"),Format([phys office visit in],"Standard")) Aug 14 '08 #17

 Expert 2.5K+ P: 3,532 I agree with everything you've said, Ade! I was simply pointing out why the OP's code, which at first glance looked valid, failed. Linq ;0)> Aug 14 '08 #18

 P: 38 Originally you had 2 closing parenthesis ")" at the end of the second Format function, and nothing at the end of first. Try this: Expand|Select|Wrap|Line Numbers Phy Office Visit In: IIf([phys office visit in]<=1,Format([phys office visit in],"Percent"),Format([phys office visit in],"Standard")) ---- That worked!!! You are awesome! Thank you! Currency even works! You have no idea how much easier this is going to make my life. Thanks again. Aug 14 '08 #19

 P: 38 Okay. Query works GREAT, however: I also have a portion of the form that is data entry. Same type of field, except I need it to update based on <1 or >=1, same as in the query. Can I use that same IF statement somewhere in the form to create the same result? If they enter .7, I want it to format it to 70%, if they enter 100, I want it to format to \$100.00. Thanks! Kelly Aug 14 '08 #20

 P: 38 Okay. Query works GREAT, however: I also have a portion of the form that is data entry. Same type of field, except I need it to update based on <1 or >=1, same as in the query. Can I use that same IF statement somewhere in the form to create the same result? If they enter .7, I want it to format it to 70%, if they enter 100, I want it to format to \$100.00. Thanks! Kelly Nevermind...I figured it out. Thanks again for all your help! Aug 14 '08 #21

 Expert Mod 15k+ P: 31,186 I tried conditional formatting (which to me seemed like the perfect way to fix this problem), but it only works with Bold/Underline and colors, unless there is something I am missing? It would be nice if it worked with number formats. My problem would be solved! :) It seems you have the size of it I'm afraid. I looked in there in more detail and was surprised to find that I COULDN'T find anything to do with format strings. A shame and I'm sorry if I misled you or wasted any of your time. Aug 14 '08 #22