Multiple Number formats for Form

  • Thread starter Thread starter lsgKelly
  • Start date Start date
L

lsgKelly

I have a table that has a field with number formats. I would like those
numbers to display differently on the form based on a query (ex: if < 1,
percentage format if >1 Currency format). Is this possible? I can't store
these numbers as text because I need to use them later for calculations.

Thanks so much for the help!

Kelly
 
Try IIF and FORMAT functions -- UNTESTED --
IIF([SomeField]<1, Format([SomeField], "##.#" * 100 &"%"),
Format([SomeField], "$##.##" ))
 
Kelly,
Yes this can be done, but not sure why you would want to do this. One way
that I tested requires two boxes, one bound to the number, and probably with
the visibility turned off so you don't see it. You can size it to a very
small box and hide it somewhere on the form; reference this one for later
calculations. The second box (number2) is visible and unbound and populated
via code. In the form.current module section, which executes every time you
move to a new record, including on opening because it moves to the first
record, add this If statement:

If Me.Numbers < 1 Then
Me.Numbers2 = Format(Me.Numbers, "0.00%")
Else
Me.Numbers2 = Format(Me.Numbers, "$#,###.00")
End If
 
I think this is what I am looking for, except I would like it to format the
cell after they enter the information. The reason I am doing this: I am
creating a database for an insurance broker. Within the plan designs, there
are both dollars and percentages ($35.00 copay) or (80% covered) within a
field. When it shows up on the form, I would like it to pull the percentages
and format them as a percentage and the same with the currency fields.

I tried it in the OnCurrent Event for the form:

If Me.DedSingPDIn <= 1 Then
Me.dedsinPDIn = Format(Me.DedSingPDIn, "0.00%")
Else
Me.DedSingPDIn = Format(Me.DedSingPDIn, "$#,###.00")
End If

It doesn't seem to work. Is there a way that I can do this without creating
another field for reference? There are portions of the form that will be
entered and the others pull from a table.

Thanks for your help!
 
OK, basic database design, you are mixing two types of information into the
same field; not good. Your problem is not with the form, but with how you are
storing the data. Please take my suggestion and change your table.

You need three fields to handle this procedure.
1) a Payment type field (PTID), as a long integer
2) a Payment field as currency
3) a Rate field as a single and formated as percentage
for the co-pay, the rate will be one

You will also need to create a payment table
PTID PaymentType
1 Co-Pay
2 Covered
3 Cash
enter however many options you need.

This table will be the source for the combo box on the form where this data
is entered and you will add it to your query. After you draw the line from
the PTID in your main table, with the one in this table you can now add the
PaymentType field. The resultant query should look like this:

PTID PaymentType Payment Rate Owed Display
1 Co-Pay $35.00 1.00 $35.00 $35.00 Co-Pay
2 Covered $6543.21 0.80 $5234.57 80% Covered
3 Cash $6543.21 1.00 $6543.21 $6,543.21 Cash

To get the Owed field the query will be multiplying the payment by the rate

In the display field you would have an immediate if statement like this:
Display: iif(rate<1,Format(Rate,"0.##%"), [Payment]) &" "& [PaymentType])

If you don't want to take my suggestion, write me back and I will give you
the answer for the form boxes. However, the only thing you missed in my
example is that you need two boxes, Number1 bound to the table value and
Number2 being given the formatted value from number one. You need two boxes
on the form for it to work. One for the entry of the number and one for the
formatting. To save space, you can put them on top of each other and have the
have them switch around. Again, write back if you want those list of
commands. I hope this helps.
 
I generally agree that you probably should have two/three
fields in the table. As long as <=1 can be used to
determine if the value is a percent or an amount, it is not
really necessary to change the table.

If you do not want or can't change the table, use two text
boxes. However, you do not need that kind of code. In
fact, you can not use that kind of code if the form is
displayed in continuous or datasheet view.

Place the formatting text box with the expression:

=IIf(DedSingPDIn <= 1, Format(DedSingPDIn, "0.00%"),
Format(DedSingPDIn, "$#,###.00"))

exactly on top of the DedSingPDIn text box.

Use this line of code in the formatting text box's GotFocus
event:
Me.DedSingPDIn.SetFocus

When the DedSingPDIn text box receives the focus, it will
automatically jump in front of the formatting text box so
the number can be edited normally. When the focus moves
away, the formatting text box will move back to the front.
 
That works GREAT! Thank you so much, everyone, for your help and responses!

Kelly

Marshall Barton said:
I generally agree that you probably should have two/three
fields in the table. As long as <=1 can be used to
determine if the value is a percent or an amount, it is not
really necessary to change the table.

If you do not want or can't change the table, use two text
boxes. However, you do not need that kind of code. In
fact, you can not use that kind of code if the form is
displayed in continuous or datasheet view.

Place the formatting text box with the expression:

=IIf(DedSingPDIn <= 1, Format(DedSingPDIn, "0.00%"),
Format(DedSingPDIn, "$#,###.00"))

exactly on top of the DedSingPDIn text box.

Use this line of code in the formatting text box's GotFocus
event:
Me.DedSingPDIn.SetFocus

When the DedSingPDIn text box receives the focus, it will
automatically jump in front of the formatting text box so
the number can be edited normally. When the focus moves
away, the formatting text box will move back to the front.
--
Marsh
MVP [MS Access]

I think this is what I am looking for, except I would like it to format the
cell after they enter the information. The reason I am doing this: I am
creating a database for an insurance broker. Within the plan designs, there
are both dollars and percentages ($35.00 copay) or (80% covered) within a
field. When it shows up on the form, I would like it to pull the percentages
and format them as a percentage and the same with the currency fields.

I tried it in the OnCurrent Event for the form:

If Me.DedSingPDIn <= 1 Then
Me.dedsinPDIn = Format(Me.DedSingPDIn, "0.00%")
Else
Me.DedSingPDIn = Format(Me.DedSingPDIn, "$#,###.00")
End If

It doesn't seem to work. Is there a way that I can do this without creating
another field for reference? There are portions of the form that will be
entered and the others pull from a table.
Click to expand...
Click to expand...
 
Back
Top