export report to word

[Guest]

I have set up large table and forms with multiple fields. Some of the field
would contain several lines of text . I have created report, but it trunkates
all information. Even when I export this report into Word, the fields are
still trunkated. How can I avoid this.
ALso, I can not figure out how to avoid printing empty fields. Is there any
way.
Thank

 

[Allen Browne]

The memo fields will truncate at 255 characters if Access has to perform any
grouping or comparion on them.

If the report is based on a query, avoid using DISTINCT, or a GROUP BY
clause. If you need to use a GROUP BY clause (Totals row in the query design
grid) then, choose First under the memo fields instead of Group By. This way
Access can return the whole memo instead of having to group by that field
(which truncates it.) You will need to rename the text boxes in the report
and change their control source to FirstOfMyMemo or whatever.

To avoid printing blank fields, the best solution is not to have lots of
blank fields. If you have repeating fields such as Week1, Week2, Week3, ...
these should become a related table with lots of records (one for each week,
with a foreign key relating back to the first table), instead of lots of
fields.

You can also set the CanShrink property of a text box to Yes in the report.
If there is nothing else overlapping the control vertically, it can then
shrink to zero height, suppressing the space it would have taken up on the
report. If you also need to suppress its attached label, right-click the
label and choose Change To | Text Box. Then set the ControlSource of the new
text box to the text that would have been in the Caption of the label, e.g.:
="City:"

 

[Guest]

for some reason this method it works only on few field. I have multple text
fiedls and mulple comboboxes that I also converted to text boxes) with labels
(that I have converted to test boxes per your advise) . I can not figure out
why I am getting such a stange behavior. Properties for the fields are the
same.

I really need to figure out how to avoid printing labels for the empty
fields as well waisting page space for empty fields.
Thanks

 

[Allen Browne]

Some things that can prevent the space from shrinking:

1. You have controls that overlap each other.

2. You have other fields that are partially beside the one that should
shrink, and partially beside others above or below.

3. You did not set the Can Shrink property of the Section to No, as well as
the controls.

4. The control contains other characters:
Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])

5. The control contains a zero-length string. If you have a Text field in
your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.

 

[Guest]

1. You have controls that overlap each other.
None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should

shrink, and partially beside others above or below.

Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well as

the controls.

I did not have it. I did enable it , but it did not help.
4. The control contains other characters:

Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])

I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in

your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.

With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

Any other suggestions, Please?

 

[Allen Browne]

After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

1. You have controls that overlap each other.

None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should

shrink, and partially beside others above or below.

Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as

the controls.

I did not have it. I did enable it , but it did not help.
4. The control contains other characters:

Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])

I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in

your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.

With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

Any other suggestions, Please?

 

[Guest]

Ok, I have set up ZLS to no, deleted all records in table, that is linked to
report, set up Can Shrink to Yes in properties of several fields (for
testing) and in the report details properties. And I am still getting empty
fiedls with labels
My report is set up this way:
<LABEL> Field
<Label> Field

In other words I do not have several columns on the page (as far as I know)
THanks

After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

1. You have controls that overlap each other.

None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should

shrink, and partially beside others above or below.

Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as

the controls.

I did not have it. I did enable it , but it did not help.
4. The control contains other characters:

Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])

I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in

your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.

With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

Any other suggestions, Please?

 

[Marshall Barton]

PMJI, but a ZLS will shrink a text box the same as Null
will.

I think the core issue here is that the "label" text boxes
need to have a conditional expression such as:
=IIf(Field Is Null, Null, "caption string")
--
Marsh
MVP [MS Access]

Ok, I have set up ZLS to no, deleted all records in table, that is linked to
report, set up Can Shrink to Yes in properties of several fields (for
testing) and in the report details properties. And I am still getting empty
fiedls with labels
My report is set up this way:
<LABEL> Field
<Label> Field

In other words I do not have several columns on the page (as far as I know)
THanks

After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.


"alexasha" wrote

None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should
shrink, and partially beside others above or below.
Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as
the controls.
I did not have it. I did enable it , but it did not help.
4. The control contains other characters:
Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])
I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in
your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.
With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

 

[Guest]

Where would I need to put this insertion
=IIf(Field Is Null, Null, "caption string")
I understood it should be in label properties.
BTW, should I change my label to back to label or leave it as text box?

PMJI, but a ZLS will shrink a text box the same as Null
will.

I think the core issue here is that the "label" text boxes
need to have a conditional expression such as:
=IIf(Field Is Null, Null, "caption string")
--
Marsh
MVP [MS Access]

Ok, I have set up ZLS to no, deleted all records in table, that is linked to
report, set up Can Shrink to Yes in properties of several fields (for
testing) and in the report details properties. And I am still getting empty
fiedls with labels
My report is set up this way:
<LABEL> Field
<Label> Field

In other words I do not have several columns on the page (as far as I know)
THanks

After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.


"alexasha" wrote
None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should
shrink, and partially beside others above or below.
Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as
the controls.
I did not have it. I did enable it , but it did not help.
4. The control contains other characters:
Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])
I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in
your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.
With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

 

[Marshall Barton]

As Allen said before, you need to use that expression in the
Control Source property of the text boxes you're using as
labels. I believe you currently have the Control Source set
to something like ="caption string"

If you change the "label" text boxes back to label controls,
you would have to use a different approach that employs a
line of VBA code for each data text box. I think Allen got
you started down the right path, so no need to change back
at this point.
--
Marsh
MVP [MS Access]

Where would I need to put this insertion
=IIf(Field Is Null, Null, "caption string")
I understood it should be in label properties.
BTW, should I change my label to back to label or leave it as text box?

PMJI, but a ZLS will shrink a text box the same as Null
will.

I think the core issue here is that the "label" text boxes
need to have a conditional expression such as:
=IIf(Field Is Null, Null, "caption string")

Ok, I have set up ZLS to no, deleted all records in table, that is linked to
report, set up Can Shrink to Yes in properties of several fields (for
testing) and in the report details properties. And I am still getting empty
fiedls with labels
My report is set up this way:
<LABEL> Field
<Label> Field

In other words I do not have several columns on the page (as far as I know)
THanks

:
After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.


"alexasha" wrote
None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should
shrink, and partially beside others above or below.
Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as
the controls.
I did not have it. I did enable it , but it did not help.
4. The control contains other characters:
Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])
I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in
your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.
With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

 

[Guest]

so, the string for my label text box would look like
="label something"=IIf(Field Is Null, Null, "caption string")
Or should I place
=IIf(Field Is Null, Null, "caption string") and replace caption string with
my label

As Allen said before, you need to use that expression in the
Control Source property of the text boxes you're using as
labels. I believe you currently have the Control Source set
to something like ="caption string"

If you change the "label" text boxes back to label controls,
you would have to use a different approach that employs a
line of VBA code for each data text box. I think Allen got
you started down the right path, so no need to change back
at this point.
--
Marsh
MVP [MS Access]

Where would I need to put this insertion
=IIf(Field Is Null, Null, "caption string")
I understood it should be in label properties.
BTW, should I change my label to back to label or leave it as text box?

PMJI, but a ZLS will shrink a text box the same as Null
will.

I think the core issue here is that the "label" text boxes
need to have a conditional expression such as:
=IIf(Field Is Null, Null, "caption string")


alexasha wrote:
Ok, I have set up ZLS to no, deleted all records in table, that is linked to
report, set up Can Shrink to Yes in properties of several fields (for
testing) and in the report details properties. And I am still getting empty
fiedls with labels
My report is set up this way:
<LABEL> Field
<Label> Field

In other words I do not have several columns on the page (as far as I know)
THanks

:
After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.


"alexasha" wrote
None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should
shrink, and partially beside others above or below.
Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as
the controls.
I did not have it. I did enable it , but it did not help.
4. The control contains other characters:
Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])
I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in
your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.
With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

 

[Guest]

I got it
=IIf(Field Is Null, Null, "caption string")
where field is my text field and caption string is my label text.
It looks like it works.
GREAT THANKS

As Allen said before, you need to use that expression in the
Control Source property of the text boxes you're using as
labels. I believe you currently have the Control Source set
to something like ="caption string"

If you change the "label" text boxes back to label controls,
you would have to use a different approach that employs a
line of VBA code for each data text box. I think Allen got
you started down the right path, so no need to change back
at this point.
--
Marsh
MVP [MS Access]

Where would I need to put this insertion
=IIf(Field Is Null, Null, "caption string")
I understood it should be in label properties.
BTW, should I change my label to back to label or leave it as text box?

PMJI, but a ZLS will shrink a text box the same as Null
will.

I think the core issue here is that the "label" text boxes
need to have a conditional expression such as:
=IIf(Field Is Null, Null, "caption string")


alexasha wrote:
Ok, I have set up ZLS to no, deleted all records in table, that is linked to
report, set up Can Shrink to Yes in properties of several fields (for
testing) and in the report details properties. And I am still getting empty
fiedls with labels
My report is set up this way:
<LABEL> Field
<Label> Field

In other words I do not have several columns on the page (as far as I know)
THanks

:
After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.


"alexasha" wrote
None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should
shrink, and partially beside others above or below.
Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as
the controls.
I did not have it. I did enable it , but it did not help.
4. The control contains other characters:
Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])
I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in
your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.
With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

 

[Guest]

Well it worked to get rid of empty labels, but I still left with empty space
in report.
I tried to set up height of fields 0.0007", but I did not place field close
to each other because I am afraid that I would not have space to grow. Any
other suggestions. We are close to solve this thing

I got it
=IIf(Field Is Null, Null, "caption string")
where field is my text field and caption string is my label text.
It looks like it works.
GREAT THANKS

As Allen said before, you need to use that expression in the
Control Source property of the text boxes you're using as
labels. I believe you currently have the Control Source set
to something like ="caption string"

If you change the "label" text boxes back to label controls,
you would have to use a different approach that employs a
line of VBA code for each data text box. I think Allen got
you started down the right path, so no need to change back
at this point.
--
Marsh
MVP [MS Access]

Where would I need to put this insertion
=IIf(Field Is Null, Null, "caption string")
I understood it should be in label properties.
BTW, should I change my label to back to label or leave it as text box?


:
PMJI, but a ZLS will shrink a text box the same as Null
will.

I think the core issue here is that the "label" text boxes
need to have a conditional expression such as:
=IIf(Field Is Null, Null, "caption string")


alexasha wrote:
Ok, I have set up ZLS to no, deleted all records in table, that is linked to
report, set up Can Shrink to Yes in properties of several fields (for
testing) and in the report details properties. And I am still getting empty
fiedls with labels
My report is set up this way:
<LABEL> Field
<Label> Field

In other words I do not have several columns on the page (as far as I know)
THanks

:
After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.


"alexasha" wrote
None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should
shrink, and partially beside others above or below.
Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as
the controls.
I did not have it. I did enable it , but it did not help.
4. The control contains other characters:
Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])
I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in
your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.
With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

 

[Marshall Barton]

Well, there is empty space you left in the report's design
and there was the empty space from the labels/text boxes
that didn't shrink.

Which empty space are you complaining about now? If it's
the space between the controls, then move the control's Top
up to the bottom of the control above it.
--
Marsh
MVP [MS Access]

Well it worked to get rid of empty labels, but I still left with empty space
in report.
I tried to set up height of fields 0.0007", but I did not place field close
to each other because I am afraid that I would not have space to grow. Any
other suggestions. We are close to solve this thing

I got it
=IIf(Field Is Null, Null, "caption string")
where field is my text field and caption string is my label text.
It looks like it works.
GREAT THANKS

As Allen said before, you need to use that expression in the
Control Source property of the text boxes you're using as
labels. I believe you currently have the Control Source set
to something like ="caption string"

If you change the "label" text boxes back to label controls,
you would have to use a different approach that employs a
line of VBA code for each data text box. I think Allen got
you started down the right path, so no need to change back
at this point.


alexasha wrote:
Where would I need to put this insertion
=IIf(Field Is Null, Null, "caption string")
I understood it should be in label properties.
BTW, should I change my label to back to label or leave it as text box?


:
PMJI, but a ZLS will shrink a text box the same as Null
will.

I think the core issue here is that the "label" text boxes
need to have a conditional expression such as:
=IIf(Field Is Null, Null, "caption string")


alexasha wrote:
Ok, I have set up ZLS to no, deleted all records in table, that is linked to
report, set up Can Shrink to Yes in properties of several fields (for
testing) and in the report details properties. And I am still getting empty
fiedls with labels
My report is set up this way:
<LABEL> Field
<Label> Field

In other words I do not have several columns on the page (as far as I know)
THanks

:
After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.


"alexasha" wrote
None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should
shrink, and partially beside others above or below.
Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as
the controls.
I did not have it. I did enable it , but it did not help.
4. The control contains other characters:
Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])
I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in
your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.
With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.

 

[Guest]

Snapping text boxes together practically removed empty space in the report
and in word file. If I skip 3-4 field it is still noticable, but not to a big
extend. I do not think I can remove it completely. Thank you for your help. I
hope I am done with this part.

Well, there is empty space you left in the report's design
and there was the empty space from the labels/text boxes
that didn't shrink.

Which empty space are you complaining about now? If it's
the space between the controls, then move the control's Top
up to the bottom of the control above it.
--
Marsh
MVP [MS Access]

Well it worked to get rid of empty labels, but I still left with empty space
in report.
I tried to set up height of fields 0.0007", but I did not place field close
to each other because I am afraid that I would not have space to grow. Any
other suggestions. We are close to solve this thing

I got it
=IIf(Field Is Null, Null, "caption string")
where field is my text field and caption string is my label text.
It looks like it works.
GREAT THANKS


:
As Allen said before, you need to use that expression in the
Control Source property of the text boxes you're using as
labels. I believe you currently have the Control Source set
to something like ="caption string"

If you change the "label" text boxes back to label controls,
you would have to use a different approach that employs a
line of VBA code for each data text box. I think Allen got
you started down the right path, so no need to change back
at this point.


alexasha wrote:
Where would I need to put this insertion
=IIf(Field Is Null, Null, "caption string")
I understood it should be in label properties.
BTW, should I change my label to back to label or leave it as text box?


:
PMJI, but a ZLS will shrink a text box the same as Null
will.

I think the core issue here is that the "label" text boxes
need to have a conditional expression such as:
=IIf(Field Is Null, Null, "caption string")


alexasha wrote:
Ok, I have set up ZLS to no, deleted all records in table, that is linked to
report, set up Can Shrink to Yes in properties of several fields (for
testing) and in the report details properties. And I am still getting empty
fiedls with labels
My report is set up this way:
<LABEL> Field
<Label> Field

In other words I do not have several columns on the page (as far as I know)
THanks

:
After setting the Allow Zero Length property, it is still possible that
existing records have a ZLS instead of a null. They could be changed with an
update query.

A more useful approach might be to examine the controls that don't work, and
see if the values are in fact Null. You then know whether the problem is
with the data or with the report.

You probably DO want the section's Can Shrink property set to Yes, so the
section height reduces.

Not sure what other ideas I can suggest.


"alexasha" wrote
None of the fields are overlaping, each field starts on new line
2. You have other fields that are partially beside the one that should
shrink, and partially beside others above or below.
Not that I can see, again, each field starts on new line
3. You did not set the Can Shrink property of the Section to No, as well
as
the controls.
I did not have it. I did enable it , but it did not help.
4. The control contains other characters:
Example 1:
=[FirstName] & " " & [Surname]
That contains a space. Fix it with Trim()
=Trim([FirstName] & " " & [Surname])
I do not have any need for this option.

5. The control contains a zero-length string. If you have a Text field in
your table, and its Allow Zero Length property is set to Yes, then it may
contain a zero-length string instead of a Null.
With this option, I changed Allow Zero Lenght to No in about 10 fields,
access has tested this is accepted this setting. Again, it did not help
removing unfilled fields.