I’ll start by saying I know next to nothing about Access; I know it’s a database system and pretty much everything else I know about it I have learned while trying to sort this issue out. If any suggestions are to input code anywhere, please tell me exactly where to put it, step by step.
Now the problem:
I am trying to help a friend with his database (I work in IT, network and infrastructure mostly, but as per most layman’s views of people in IT I MUST know how to do everything on every conceivable program known to man).
This database has a table view and a form view. In the form view we have some fields that may not always contain data for every record. For example if it was a database on employees there would be fields on ‘Spouse’ and ‘Kids’, etc etc. Not all employees are going to be married or have kids.
We would like the empty fields to disappear altogether from the form view for these particular records, but obviously be there for records where those fields are populated.
I have googled for a fair few hours but not hit the jackpot yet;
Many suggestions on forums say to use the ‘can shrink’ and ‘can grow’ attributes on the field – this doesn’t work. Some people have suggested combining ‘can shrink’ with ‘visible:no’ – using ‘visible:no’ hides the field altogether, populated or not.
Also nowhere does it mention if the field name can have a space or not – I have tried the following code in the ‘control source’ attribute, both with the field name with a space, and an under_score where the space would be, giving different results
=IIf([City] Is Null, Null, "City:") - obviously changed ‘city’ to my field name. with a space in the field name it gives an error: You Have entered an Operand without an operator…?
With an underscore instead of a space it merely enters the field name into the box for all records.
I got the code from here - http://www.eggheadcafe.com/software/aspnet/32714746/access-hide-blank-fields-and-captions.aspx
I have also seen a few suggestions to input vb code – I have found where to open the database in vbs, but can’t see how to enter code for it
Any pointers would be greatly appreciated.
please let me know if that's as clear as mud and you need anything else explaining