469,573 Members | 1,648 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,573 developers. It's quick & easy.

Placing string "None" in blank report textboxes

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...
Sep 10 '09 #1
3 2560
2,653 Expert 2GB
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,
Sep 10 '09 #2
Stewart Ross
2,545 Expert Mod 2GB
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.

Sep 10 '09 #3
Stewart Ross
2,545 Expert Mod 2GB
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...

Sep 10 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Sam Sungshik Kong | last post: by
5 posts views Thread by Sue | last post: by
2 posts views Thread by spifster | last post: by
2 posts views Thread by Good Man | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.