Custom formatting a calculated text field on a form fails

[JeffChr]

I have a form with a select query recordset, which includes a couple calculated fields. The calculations are done in the query. I calculated the fields in VBA and returned a number (long) so I could sort the query on this number. Additionally, I do not want zeros to display in the form, but I cannot get the Format property on the text field correctly set. Any custom format string does not work. If I use one of the pre-defined formats such as "Currency" from the property dialog combo box, it works just fine.



I have tried various custom format codes, including the "four-state" declaration (+;-;0;Null). But actually any custom format fails. For instance if I put a"#" in the format property it treats the format code literally and displays the pound sign. Thanks.

[June7]

I am confused. You say the calculation is in the query but then say the calculation is in VBA - the query calls a custom function? If the function returns a long, why would you say this is a text field? Why would you need to apply format in query?

[JeffChr]

I have this resolved, but thanks for replying. The calcs are "performed" in the query via a function call. I misspoke on the the "text field" and should have said "text box", as in on the form. Which leads to the final question - obviously not applying the format to the query. Sorry for all the confusion. Next time I'll try to be more precise.

And I have no explanation why it started working. After playing around a bit I put the "#" back into the text BOX format property (without the quotes) and got the results I was looking for. But I don't know why it didn't work many times before that. I changed nothing else.

[June7]

What do you mean by 'do not want zeros' - no decimal part? If the value returned by function is truly a number and not a string, set textbox Format to Fixed and DecimalPlaces to 0.

[JeffChr]

As I mentioned earlier, this issue is resolved. But to address your question, I do not want zeroes to display-just blanks. I accomplished this by formatting the number with just a pound sign in the format property. Thanks for your assistance.

[June7]

Thanks for clarifying. Think I understand now. I probably would not have thought of the Format solution. Probably would have used an IIf expression in ControlSource.