?
Solved

format Currency on Variable for Message box

Posted on 2014-03-05
6
Medium Priority
?
587 Views
Last Modified: 2014-03-06
what is the proper syntax to display the value in a MsgBox as currency ( $10,000.00)  As you can see I have tried 2 different methods, neither successful.

Private Sub NetAmount_AfterUpdate()
   On Error GoTo NetAmount_AfterUpdate_Error

Dim nAmt As Long
Dim nAmtTotal As Long

nAmt = Format(Me.NetAmount, "Currency")
nAmtTotal = FormatCurrency(Forms![frmCodingSlip]![txtMCFRemainingUS].value, 2)
    If nAmt > nAmtTotal Then
        Select Case _
            MsgBox("The system is unable to process the current invoice due to the Remaining Funds of " _
            & nAmtTotal & " is less than the amount of entered." _
            & vbCrLf & "" _
            & vbCrLf & "Please validate and adjust the amount enter that is no greater than the Remaining Funds." _
            , vbOK Or vbCritical Or vbDefaultButton1, "Funds Discrepancy")
        
            Case vbOK
                Me.NetAmount = vbNullString
                Exit Sub
        End Select
    Else
        Me.Currency.SetFocus
        Me.Currency.Dropdown
    End If
   On Error GoTo 0
   Exit Sub

NetAmount_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure NetAmount_AfterUpdate of VBA Document Form_frmInvoiceSub"
End Sub

Open in new window

0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Expert Comment

by:gdarcan
ID: 39907812
You may use FormatNumber function. Please refer to w3schools
0
 

Author Comment

by:Karen Schaefer
ID: 39907885
sorry that does not get me currency ($10,000.00)
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39907993
When you Format() a numeric value you make it a string so $10,000 would be less than $2.  Do the compare using the unformatted fields.  Then format for display.  Formatting is for humans, not computers.
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39908566
Yes, you have got this wrong. Here is how (air code):
Private Sub NetAmount_AfterUpdate()
   On Error GoTo NetAmount_AfterUpdate_Error

Dim nAmt As Currency
Dim nAmtTotal As Currency

If IsNull(Me!NetAmount) Then Exit Sub

nAmt = CCur(Me!NetAmount)
nAmtTotal = CCur(Format(Forms![frmCodingSlip]![txtMCFRemainingUS].Value, "0.00"))
    If nAmt > nAmtTotal Then
        Select Case _
            MsgBox("The system is unable to process the current invoice due to the Remaining Funds of " _
            & Format(nAmtTotal, "Currency") & " is less than the amount of entered." _
            & vbCrLf & "" _
            & vbCrLf & "Please validate and adjust the amount enter that is no greater than the Remaining Funds." _
            , vbOK Or vbCritical Or vbDefaultButton1, "Funds Discrepancy")
        
            Case vbOK
                Me!NetAmount = Null
                Exit Sub
        End Select
    Else
        Me!Currency.SetFocus
        Me!Currency.Dropdown
    End If
    On Error GoTo 0
    Exit Sub

NetAmount_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure NetAmount_AfterUpdate of VBA Document Form_frmInvoiceSub"
End Sub

Open in new window

/gustav
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39910023
Select Case _
            MsgBox("The system is unable to process the current invoice due to the Remaining Funds of " _
            & Format(nAmtTotal, "Currency") & " is less than the amount of entered." _
            & vbCrLf & "" _
            & vbCrLf & "Please validate and adjust the amount enter that is no greater than the Remaining Funds." _
            , vbOK Or vbCritical Or vbDefaultButton1, "Funds Discrepancy")
       

this did the trick Thanks.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39911773
You are welcome!

/gustav
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

741 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