Text Box Visibility?

[LHolden]

I currently have a form which serves as an invoice. It pulls information from a query and a form, and creates an invoice from them. Certain text boxes on the report don't need to be there if the value within them is 0. I'm trying to figure out how to get access to recognize to make the visibility property = 0 when the value in the box is 0. So far I have tried adding a line to the button which opens the report (access macro, not VBA), using the setproperty value, where control name is 'Textbox', and visibility argument is something like 'Switch([Textbox]=0, 0, [Textbox]>0, -1), but this results in a textbox which is always invisible.

Any help is greatly appreciated.

 

[vbaInet]

You don't need to hide the textbox. Use an IIF() statement to set the value to Null if it's 0.

 

[LHolden]

That's brilliant! Can't believe I didn't think of it. Just having a slight problem trying to figure out where to put the IIF statement. Could you help me with that too?

 

[vbaInet]

You can put it in the Control Source of the textbox that's bound to the field, or put it in the report's Record Source. I'm guessing the Record Source is a query.

 

[LHolden]

Been trying it, but when I go to the Query and enter the IIF statement in the criteria for the control field - iif([Control]=0,Is Null) - i get a type mismatch.

 

[LHolden]

Got it! Thanks so much for your help

 

[vbaInet]

E.g.:

IIF([Field] = 0, Null, [Field])

Remember I mentioned you should use Null, not Is Null.

 

[LHolden]

Yea, I figured that out and got it all worked out. Thanks so much for your time and help, I really appreciate it.

 

[vbaInet]

Yep, I only just noticed. Good job!