Multiple Number formats

[LSGKelly]

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!)

[hjozinovic]

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(PaymentTyp e="Fixed";[Charge]+[Amount];[Charge]*(1+[Percent]))

[LSGKelly]

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

[hjozinovic]

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.DedSi ngPDIn, "0.00%")
Else
Me.DedSingPDIn = Format(Me.DedSi ngPDIn, "$#,###.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");For mat([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.

[NeoPa]

Look into Conditional Formatting.

It would seem perfect for this problem.

Welcome to Bytes!

[LSGKelly]

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");For mat([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

[hjozinovic]

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.

[NeoPa]

Kelly, bear in mind, that approach returns a string value rather than a numeric which is simply DISPLAYED in a particular way.

[hjozinovic]

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.

[NeoPa]

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.

[missinglinq]

Hey folks! If you look at the line # 2 of the code the OP posted

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)>

[NeoPa]

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).

[LSGKelly]

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

[LSGKelly]

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! :)

[hjozinovic]

------

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]