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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.