Placing string "None" in blank report textboxes

[Kappucino XL]

Hi there...
What is the VBA Code for inserting the string "none", in every blank textbox, in a report?
My system, generates a report from a query. eg: textbox1: FirstName ; textbox2: Middle Name; textbox3: LastName! Now, my report will display everyone's FirstName, MiddleName, LastName from the query. Incase someone does not have a middle-name, I need the word "None", to be displayed, on the the report? How do I go about doing this?
My attempt : If txtMidName.Value = " " Then txtMidName.Value = "None"
But clearly this didn't work
Please Help...

[FishVal]

You could well help yourself debugging your code.
Hint for starters:

• Put breakpoint on the mulfunctioning code line.
• Let code run.
• When execution stops (if ever) on the breakpoint check values returned by properties, variables, expression evaluation results etc involved.
• ??????
• PROFIT !!!

Kindest regards,
Fish.

[Stewart Ross]

Assuming that it is not a fault you are trying to trace - you just want to replace null fields (fields with no content at all) with the word "none" - you need to change the textboxes concerned in your report from being bound to an underlying field to being unbound, then set their control source to a call to the Nz function applied to your original bound field. This is not a VBA issue, and in any event you cannot change the contents of bound report controls using VBA code (although you can of course change the contents of unbound controls).

Say your textbox was called "TextBox2" and its control source is field "Middlename". All you'd need to do is to change the controlsource property to

=Nz([Middlename], "(none)")

Note that it is rare for such a blank field to contain an empty string and not simply be null - there is a considerable difference. If it really does contain an empty string then Nz will not work (as the field is not null) in which case you can use an IIF statement instead:

=IIF([Middlename]="", "(none)", [Middlename])

Please also note that if the name of the textbox is the same as the bound field - which it will be if it was created by the report wizard - you must change it to a name that does not clash with the name of an existing field or an error will result.

-Stewart

[Stewart Ross]

Final thought from me on this one is that it is often a more general approach to put such computed fields into your report's source query (using the IIF or Nz approach as necessary), then use the computed field in your report instead of the potentially blank one. This has the benefit that you create the computed field just once and can use it wherever you use your query - in a form, a report or whatever.

The choice is yours...

-Stewart