Trim a textbox in a sub-report...

[Andrew Meador]

I have a sub-report based on a query. A couple of the fields (being
pulled from a SQL Server Express 2005 back end) are padded with spaces
at the end. I want to trim these spaces off for the report I'm
creating, but I can't figure our where/how to trim these spaces off.

I've tried setting the Control Source for the text boxes on the
report to '=trim(fieldname)', but this causes an error of "This
control has a reference to itself" and the field shows "#Error" when
you open the report in report view.

I've tried creating code for the detail section of the report and
for various events of the text boxes, but have not had success - maybe
I haven't tried the right event? I don't know.

Your input would be appreciated - Thanks!

 

[Franck]

I have a sub-report based on a query. A couple of the fields (being
pulled from a SQL Server Express 2005 back end) are padded with spaces
at the end. I want to trim these spaces off for the report I'm
creating, but I can't figure our where/how to trim these spaces off.

I've tried setting the Control Source for the text boxes on the
report to '=trim(fieldname)', but this causes an error of "This
control has a reference to itself" and the field shows "#Error" when
you open the report in report view.

I've tried creating code for the detail section of the report and
for various events of the text boxes, but have not had success - maybe
I haven't tried the right event? I don't know.

Your input would be appreciated - Thanks!

the easiest way would be to trim in the field in a query, as a SQL
function.
create a query and to not complicate your life with left or right trim
use replace(field,' ','') which will replace space by nothing which
come to same thing as trim except it also remove space in the middle.

 

[Andrew Meador]

Thanks Franck! That worked fine, I was working with fields that
should have no spaces in them, so a replace works fine. It took a
little work to figure out where to make this change, but I did it. I
just changed the SQL that Access generated, to include the fields from
my original query, and added in the replace function there. Seems odd
to me that Access generates another query when my sub-report was based
on a query in the first place, but oh well - its done. Thanks!