format Currency on Variable for Message box

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

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
gdarcanCommented:
You may use FormatNumber function. Please refer to w3schools
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
sorry that does not get me currency ($10,000.00)
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
PatHartmanCommented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.