Solved

format Currency on Variable for Message box

Posted on 2014-03-05
6
559 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 37

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 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 50

Expert Comment

by:Gustav Brock
ID: 39911773
You are welcome!

/gustav
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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