473,320 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Multiple Number formats

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 1658
hjozinovic
167 100+
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
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
  1. If Me.DedSingPDIn <= 1 Then
  2.     Me.dedsinPDIn = Format(Me.DedSingPDIn, "0.00%")
  3.     Else
  4.     Me.DedSingPDIn = Format(Me.DedSingPDIn, "$#,###.00")
  5. End If
in the OnCurrent Event of the form, but it doesn't seem to work. Any ideas?

Thanks,

Kelly
Aug 12 '08 #3
hjozinovic
167 100+
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
NeoPa
32,556 Expert Mod 16PB
Look into Conditional Formatting.

It would seem perfect for this problem.

Welcome to Bytes!
Aug 13 '08 #5
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
hjozinovic
167 100+
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
NeoPa
32,556 Expert Mod 16PB
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
hjozinovic
167 100+
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
NeoPa
32,556 Expert Mod 16PB
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
missinglinq
3,532 Expert 2GB
Hey folks! If you look at the line # 2 of the code the OP posted

Expand|Select|Wrap|Line Numbers
  1. If Me.DedSingPDIn <= 1 Then
  2.     Me.dedsinPDIn = Format(Me.DedSingPDIn, "0.00%")
  3.     Else
  4.     Me.DedSingPDIn = Format(Me.DedSingPDIn, "$#,###.00")
  5. 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
NeoPa
32,556 Expert Mod 16PB
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
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
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
hjozinovic
167 100+
------

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
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
hjozinovic
167 100+
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
  1. 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
missinglinq
3,532 Expert 2GB
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
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
  1. 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
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
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
NeoPa
32,556 Expert Mod 16PB
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

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Indra | last post by:
Hi There, I am looking for information on how to import the txt or csv file to the multiple table in sql 2000. If you have any kind of inf. please let me know wheather we can do this and how. ...
4
by: Richard Hollenbeck | last post by:
I'm trying to write some code that will convert any of the most popular standard date formats twice in to something like "dd Mmm yyyy" (i.e. 08 Jan 1908) and compare the first with the second and...
2
by: Auronc | last post by:
In VC, the data type "double" has 8-bytes length. For calculating precisely,I have to make more long floating-point,like 16bytes or more. Somebody could give me a hint or maybe some free source...
2
by: Subodh | last post by:
Hi, Currently we get data from more then 200 different sources and all of our vendors provide data in different file formats. The problem is we have more then 100 DTS packages now and the...
13
by: a.zeevi | last post by:
free() multiple allocation error in C ==================================== Hi! I have written a program in C on PC with Windows 2000 in a Visual C environment. I have an error in freeing...
2
by: marklawford | last post by:
I've been prototyping a reporting solution using XSLT and Java to transform a number of XML files into different formats (PDF and CSV mainly). The XML comes from a legacy system where a number of...
1
by: feltra | last post by:
Hi, The following is from my friend who has only restricted net access from his office and hence cannot post.... ...
0
by: feltra | last post by:
Hi all, I am trying to export a GridView data to multiple file formats. The requirement is that when more than one file format is selected and the "Submit" button is clicked, the data from the...
1
by: tbrogdon | last post by:
I am new to this group and new to DBs. I am building a small DB for my work. We create sheetmetal parts. Each part has a part number (e.g., 1054471 or 50TG508506 - both formats are typical for a...
3
by: Jordan S. | last post by:
I would appreciate your thoughts and opinions on the following: I am looking to render documents into multiple document formats. At a minimum the documents will need to be rendered as HTML, and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.