Solved

Money Format Conversion

Posted on 2004-04-26
43
462 Views
Last Modified: 2008-02-01
I have a textbox where a large dollar about is entered in millions ( 1.9M = $1,900,000 ). I need the entry to be converted to $1,500,000 format from 1.5M when the textbox loses focus OR on submit. It needs to be saved in the database as $1,500,000 and not 1.5M. How would I go about doing this. Not sure what to do with the code.

thanks.
0
Comment
Question by:Lance_Frisbee
  • 27
  • 15
43 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 10922212
select case Right(Amount,1)
   CAse "B"
        Val(Amount) * 1000000000
   Case "M"
        Val(Amount) * 1000000
end select

Something like that?


0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10922900
Maybe... I'm kind of messing around with something like this right now: I can't figure out how to see if a string contains a certain character like a "." Here's what i have:


    Private Sub btnMoney_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoney.Click
        Dim Input As String
        ' Dim Middle As String

        Input = txtMoney.Text

        If Input.EndsWith("M") = True Then
            ' If Input.IndexOf(".") = "-1" Then
            ' Input.Replace("M", ",000,000")
            ' End If
        If Input.StartsWith("0") Then
            Input = Input.Replace(".", "")
            Input = Input.Replace("0", "")
        Else
            Input = Input.Replace(".", ",")
        End If
        Input = Input.Replace("M", "")
        Input = Input.Concat(Input, "00,000")
        End If

        If Input.EndsWith("B") = True Then
            ' If Input.IndexOf(".") = "-1" Then
            ' Input.Replace("B", ",000,000,000")
            ' End If
            If Input.StartsWith("0") Then
                Input = Input.Replace(".", "")
                Input = Input.Replace("0", "")
            Else
                Input = Input.Replace(".", ",")
            End If
            Input = Input.Replace("B", "")
            Input = Input.Concat(Input, "00,000,000")
        End If


        Input = Input.Insert(0, "$")
        lblMoney.Text = Input

    End Sub
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10922925
I know by no means is this perfect code at all... i am just trying to get something roughly functioning - THEN i'm going to go back and fine tune it - and troubleshoot.
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10925334
Hi Lance_Frisbee,
Are all values in millions?
If that is the case, why not simply have a separate textbox with the 'M' being in a label after it?
That way the user will enter 1.5 (the M is already there). All you need to do is to multiply by a million as suggested by Mikal.
Do not be concerned about the comma, the $ sign or if it starts with a 0, etc.
If you have set your database field as being numeric, the value stored is a number, not a string.
You can then DISPLAY this number in any application anywhere you want.

If you also require the Billions on top of the Millions, then Mikal's solution appears to be the right way to go.
Why complicate a simple solution?

Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10932478
That's a good suggestion... i'll throw that out there and see if it works out-- i'll get back to you

THanks.
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10942741
I checked with my client - It MUST be entered in : 1.5M.

So now on TOP of the conversion - I also need a regular expression validator that is something like:

^\d{1,2,3}\.\d{1}\M$

Thats not EVEN close but it needs to accept 1-3 digits i.e. 304, 59, or 4 - then contain a period (.) - then follow that with ONE digit and an 'M'

103.3M is valid - 103M is not.
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10942964
Lance_Frisbee:
Bugger!
But yes RegExp is the way to go.

Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10943912
Anyone help with the RegExp? I can't get one working :(
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953127
   Private Sub btnMoney_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoney.Click

        Dim Input As String

        Input = txtMoney.Text

        Input = Input.Replace("M", "")
        Input = Input * 1000000

        Input = Input.Insert("0", "$")

        lblMoney.Text = Input


    End Sub

I have this for conversion and it works great... BUT! I need someone to tell me how to add commas in the correct places.
0
 
LVL 27

Accepted Solution

by:
Dabas earned 100 total points
ID: 10953326
Lance_Frisbee:
Well done! But essentially not very different from Mikal's suggestion.
You are also not validating if the user has actually written the M in and if the format is correct, which is what you wanted to do with RegExp (I have not had the time to look at that. Still interested? Maybe increase in points????)

As to inserting into the database, there are two issues here:

Is the field in the database numeric? In that case why bother? A number is a number is a number, and for the system a number stored as 1,500,000 or as $1500000 or as H16E360 or as 101101110001101100000 it is still the same. (The computer probably will prefer the latter!)

If the field is not numeric, then big red light!!!

AS to how to insert the commas.

Dim sngInput as Single '(If numbers are really big, then consider using a Double)
sngInput = 1000000 * Val(txtMoney.Text) 'Convert to numeric - It will also remove the "M"
lblMoney.Text = sngInput.ToString("C0") 'C0: C = Currency. 0 digits after decimal point. Commas added properly automatically.
Value to be stored in the database is sngInput
Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953336
It's being used for something different. I need both the Conversion AND the validator to which i have a separate post i believe. That's the easy one that is just ALMOST working i just can't complete it.

0
 
LVL 27

Expert Comment

by:Dabas
ID: 10953360
OK. I see somebody has given you the regex already.
My code above gives you the conversion.

Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953371
LOL, it didn't email me - I didn't know I had it yet. Dabas - thank you for your help - Let me get all this working - and once it is - points will be awarded. Thank you sir.
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953527
Dabas - One more thing if you wouldn't mind,

How do I set precision to one decimal place? I am doing the same conversion but vice versa... $24,500,000 to 24.5M

It needs to only show one digit after decimal even if it's 25,403,532
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10953550
Lance_Frisbee:
C1 instead of C0

Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953553
thank you
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953578
Dang it, i'm lost again -

Am I using a single for the reverse conversion? How would I take $24,500,000 and make it say 24.5M

Would I use a string and replace? and concatenate the M? Or how?
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10953630
Dim sngShortMoney as Single
sngShortMoney = Val(lblMoney.Text.SubString(1)) 'Remove the $ sign. There are other ways to do it
sngShortMoney = sngShortMoney/1000000 'Divide by a Million
txtMoney = sngShortMoney.ToString("C1") & "M"
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953639
Thanks a bunch you're a lifesaver :)

SO much to do today
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953675
LOL - only one problem that converts whatever the dollar amount is to "$0.0M" everytime.

it has the dollar sign and no value :(

I'll assign more points soon.
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953687
OH nevermind - i see whats goin on... don't worry about it.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10953713
And i lied. It's still doing that.

        Dim sngShortMoney As Single
        sngShortMoney = Val(txtVice.Text.Substring(1)) 'Remove the $ sign. There are other ways to do it
        sngShortMoney = sngShortMoney / 1000000
        lblVice.Text = sngShortMoney.ToString("C1") & "M"

I have this: A dummy page with a label and a textbox.

I need the value in the textbox txtVice.Text to be converted then displayed in lblVice.Text

I thought i had that here. But "$0.0M" is displayed eveytime.

0
 
LVL 27

Expert Comment

by:Dabas
ID: 10954651
Lance_Frisbee,
change the first line to:
sngShortMoney = Val(Strings.Mid(txtVice.Text,2))

Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10963953
Thank you dabas: If you were interested - here's what i came up with in the end:

    Private Function ConvertXMLMoneyToDisplayMoney(ByVal XMLMoney As String) As String

        Dim Result As String = XMLMoney
        'check for blanks
        If Trim(XMLMoney) = "" Then Exit Function
        'try to convert currency to x.xM format (ie - 1500000 -> 1.5M)
        Try
            Dim sngShortMoney As Single = Val(XMLMoney / 1000000)
            Result = sngShortMoney.ToString("C1") & "M"
            Result = Result.Replace("$", "")
        Catch ex As Exception
        Finally
        End Try
        Return Result

    End Function

   

    Private Function ConvertDisplayMoneyToXMLMoney(ByVal DisplayMoney As String) As String

        Dim Result As String = DisplayMoney
        'check for blanks
        If Trim(DisplayMoney) = "" Then Exit Function
        'try to convert x.xM format to currency (ie - 1.5M -> 1500000)
        Try
            DisplayMoney = DisplayMoney.Replace("M", "")
            Dim sngInput As Single = 1000000 * Val(DisplayMoney)
            Result = sngInput.ToString("C0")
        Catch ex As Exception
        Finally
        End Try
        Return Result

    End Function
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10963979
I seem to be out of asking points - I had one important question though :(  If you would help me out - because i know you are a genius, I will make a points question for you in a few days.

I have the same type of thing - I can't find help on this anywhere. I wrote a javascript function that does one of these. But my boss insists it must be a VB Function since thats what the client stipulated. I need two functions (nearly identical).

One has to change a date from dd-MON-yyyy to dd-mm-yyyy
2nd has to change a date from dd-mm-yyyy to dd-MON-yyyy

I can't find a solution or even where to start anywhere. If this comes easy to you, i would REALLY appreciate it if you could help me out. I will reimburse you later.

Thanks,
Lance
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964000
BTW, you have taught me a lot in the last few days :) I'm still somewhat novice in VB - I am an intern right now trying to speed learn everything... they threw me right in the mix of a project for the dept. of homeland security... I am getting overloaded :o

Thanks again.
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10964259
Lance:
What is MON. Do you mean chaning from three letter month name to two digit month?
As in 01-May-2004 to 01-05-2004?

Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964264
yes, exactly:

01-JAN-2004

01-01-2004
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10964325
Lance:
Public Function MONtoMM(sDate as String)
    MONtoMM = Format(sdate, "dd-mm-yyyy")
End Function

Public MMtoMON(sDate as String)
    MMtoMON = Format(sdate, "dd-MMM-yyyy")
End Function

Dabas
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10964328
Lance_Frisbee:
Ooops... That was the VB6 answer. Just realized you are on VB.NET

Dabas
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10964362
Public Function MONtoMM(sDate as String) as String
    Dim d as Date = CDate(sDate)
    Return d.ToString("dd-MM-yyyy")
End Function

Public MMtoMON(sDate as String) as String
    Dim d as Date = CDate(sDate)
    Return d.ToString("dd-MMM-yyyy")
End Function
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964369
Thanks a ton. I'll notify you when I put up the point question.
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10964375
Lance_Frisbee:

As to your code
>       If Trim(XMLMoney) = "" Then Exit Function
This means the function does not return anything!
Better If Trim(XMLMoney) = "" Then Return ""

Also return "" between your Catch and Finally so that the calling procedure does not crash.


Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964386
my boss wrote that part lol... i'll copy him on this ;)

Thanks again
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964491
DABAS:

THIS ONE WORKS:

    Private Function MONtoMM(ByVal sDate As String) As String

        Try
            Dim d As Date = CDate(sDate)
            Return d.ToString("dd-MM-yyyy")
        Catch ex As Exception
            Return ""
        Finally
        End Try

    End Function

THIS ONE DOESN'T WORK:  WHATS WRONG?

    Private Function MMtoMON(ByVal sDate As String) As String

        Try
            Dim d As Date = CDate(sDate)
            Return d.ToString("dd-MMM-yyyy")
        Catch ex As Exception
            Return ""
        Finally
        End Try

    End Function

THE BOTTOM FUNCTION RETURNS NOTHING
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10964624
Lance_Frisbee:
Worked for me. What is the value of sDate that you sent?

Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964631
30-04-2004
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964647
+      ex      {System.InvalidCastException}      System.Exception


when i debug i get that.
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964659
I SEE. It's working when i use mm/dd/yyyy format but not dd/mm/yyyy
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10964664
Lance:

Are your regional settings set to dd/mm/yyyy or mm/dd/yyyy
(In short, do you live in North America, or in the civilized world?)

If the former (only pun intended), then 30/04/2004 might be causing the error as CDate believes you are taking the 30th month

Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964665
I just used a different format - i got it. THANK YOU!
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10964677
Lance_Frisbee:
Cool! Otherwise you will have to break the day month and year into separate strings and then rebuild it properly

Dabas
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 10964681
thanks again for your help dabas - your a life saver! I'll reward you soon :)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now