Format property for calculated control not working

[RonL]

On a report, I have a text box control whose control source is a numeric calculated field from the underlying query. The calculation uses the IIF function like this:

IIf(Nz([tblBeneficiaries.BequestContingent])=0,"",[tblBeneficiaries.BequestContingent])

I set the control's format property to currency, but it doesn't take. When the test is false, the number shows without formatting. What am I missing?



Thanks, -Ron

[CJ_London]

the "" is text, not numeric - format does not work on text values. Try changing it to 0 or perhaps Null. If you don't want a value to appear if 0 then try format like this

£#,##0.00;-£#,##0.00;""

[RonL]

Thank you! Null works. (I'd forgotten that "" is not equivalent to a null.)

Interesting that even though the calculated field is evaluated in the query, when the test results in a numeric value the format property of the text control still does not work. I understand that the Access IIF function evaluates BOTH the true and false expressions before choosing one, but would have thought that process precedes and is independent of the application of the format property of the corresponding control. Oh well, got what I want; tx again.

[CJ_London]

IIF is a vba function and in vba the whole of the expression is evaluated - which is why something like

iif(1=1,1*1,1/0) will generate a divide by zero error on compilation even if the second part if the statement is/can never be never run

A control or field can only be one type (text, number etc) so when faced with a 'choice' of text or number it will choose text since this is the option that works for both

[RonL]

At the risk of pushing this too far, but with hope for deeper understanding of how Access works:

IIF is a vba function and in vba the whole of the expression is evaluated....

But I don't have any VBA in this database - ie. have not written any code to the code modules. So you're saying even a VBA expression in the SQL parser is recognized and sent to the VBA compiler? I've been thinking of Access as having it's own engine (Jet?), with VBA being a separate engine (compiler or interpreter?) and SQL being another separate engine. I know that SQL can be embedded in VBA code, and presumably it gets recognized and sent to the SQL parser. But sounds like VBA is not separate from the main Access engine? What's the proper way to think about this?

A control or field can only be one type (text, number etc) so when faced with a 'choice' of text or number it will choose text since this is the option that works for both

Understood. Good to know.

[CJ_London]

Access has its own engine - JET for 2003 and earlier, ACE for 2007 and later. But you can use other engines such as SQL Server, MySQL etc. I confess I do not know whether IIF is evaluated by the Access SQL parser or whether it passes the evaluation on to VBA - it is slightly academic because JET/ACE only works with Access. IIF, along with many other functions that work in Access SQL do not work for SQL server, etc although they may well have equivalents.