vba format function throws type mismatch error

Question

I am trying to format a number as currency in Access VBA.

In the immediate window, when I enter:

? Format(123, "Currency")

I get the expected response: "$123.00"

However, in the code window, when I enter:

Debug.Print Format(123, "Currency")

I get an error pointing to that line: "Run-time error '13': Type mismatch"

Why does the same simple code work in the immediate window, but throw an error when run from the code window?

Answer 1

I don't see why your second example should cause an error. The following subroutine compiles and runs without error on my Access 2003 system:

Public Sub test_Format()
    Debug.Print Format(123, "Currency")
End Sub

Try that subroutine in a new database. Perhaps your current database is corrupted.

See Tony Toews' Corrupt Microsoft Access MDBs FAQ

Answer 2

What do you mean by Code Window?

This works:

Private Sub Form_Load()
       Debug.Print Format(123, "Currency")

End Sub