Currency appears as text in vba debug

[Gina Maylone]

Hello everyone!

The last line results in false when I type

?balleft >=baldue

in the immediate window. And it should not! Debug.print shows the balleft IS greater than the baldue. When I hover over baldue I get: "$164.69" WITH the quotes. Balleft is fine 400. What am I missing please? TIA!

    Dim strsql As String
    Dim baldue As Currency
    Dim balleft As Currency
    Dim payment As Currency
    Dim CRAmount As Currency
    Dim DBAmount As Currency

    baldue = Format(Me.baldue,"currency")
    balleft = Me.payment

    ' Add to payments
     DoCmd.RunSQL "INSERTINTO AsmtPayments ( PaymentAmount, MemberID, PaymentDate ) " & vbCrLf& _
    "SELECT[Forms]![AccountsandPayments]![Accountssubform].[Form]![payment] AS Expr1,Accounts.MemberID,    
    [Forms]![AccountsandPayments]![Accountssubform].[Form]![datepaid]AS Expr2 " & vbCrLf & _
    "FROM Accounts "& vbCrLf & _
    "WHERE(((Accounts.MemberID)=[Forms]![AccountsandPayments]![MemberID]));"

    DoCmd.SetWarnings True
    'Me.Requery

     'There is money and assessments to pay
    ' Find first unpaid item
   Forms![AccountsandPayments]![Accountssubform].SetFocus
   Forms![AccountsandPayments]![Accountssubform].Form![DateAssessed].SetFocus
    DoCmd.GoToRecord , , acFirst

    While balleft >=baldue Andbaldue > 0

[JoeM]

The FORMAT function returns a text value, not a numeric one.
I don't think you need to use it all here (especially since you already declaring the variable to be currency!).

Change this row:

    baldue = Format(Me.baldue,"currency")

to this:

    baldue = Me.baldue

[June7]

Format() function returns a string value, not numeric. However, I am not getting the behavior you describe. I hover over baldue and there are no quote marks nor dollar sign. My test code:

Sub test2()
Dim baldue As Currency, balleft As Currency
baldue = Format(164.69, "Currency")
balleft = 400
Debug.Print balleft >= baldue
End Sub

Why use Format() function?

Missing a space in Andbaldue - is that just a typo in post?

[Gina Maylone]

Thank you June and Joe. Yes it was a typo in my post. I added the extra "format" to try and combat the problem, obviously didn't work. I've removed the line and still get the string value.

[ArviLaanemets]

Is the reference Me.baldue to table field or to form control? Have you same name for field and control? What is format of field? What is format of control?

[Gina Maylone]

Hi ArviLaanemets! It refers to an unbound, calculated form control....aaaand....you nailed it. I had "format" on that control too! Removed it and all is well.

Thank you everyone for your input!