Field Lengths Problem

[JAJansenJr]

I have reports printing out pretty well, but am facing the issue that the
description for certain items is pretty long and does not show all that is in
the field.

Q1: Is it possible to set the field length at run time? I would traverse
the table upon which the report is based to determine the item with the
longest description and then set the field length so that everything shows on
the printed report.

Q2: Is it possible to "wrap" the longer items to fit on two lines? In
other words, rather than change the field length I would want to program the
longer items to go to a second line to print the second half of the
description.

Any and all suggestions sincerely appreciated and welcome.

 

[Jeff Boyce]

For the items that are longer than the control you are using, check out the
CanGrow and CanShrink properties. You use these to allow the text control
to expand (and contract) to fit the amount of text.

The risk in setting "the field length at run time" is that your carefully
laid out/design report will get ugly and hard to read. If you feel you
must, take a look at the OnFormat event as a place you could handle that.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[JAJansenJr]

I was thinking of using VBA to handle the printing to get a wrap around
second line to line up. I don't want to do the whole report by VBA. So I
have to decide how a VBA subroutine might be called at the right moment.

CanGrow and CanShrink might work but I'll have to try these out and see what
they actually end up doing.

I looked up the OnFormat event in the Access Help system. There were three
entries none of which seemed to pertain to my situation. Can you explain
what you had in mind?

It's very surprising to me that this issue (a value too large for a field to
print the entire item) hasn't been allowed for in the Report Object.

I may have seen something about two items being allowed in a single field.
Perhaps there is some way to take advantage of this to deal with my issue.

The big uncertainty is whether or not I can use Macros to get the report
printed with a VBA subroutine of some kind happening in the middle or whether
I have to go entirely the VBA route.

I suppose I could use the tool that generates VBA code from a Macro and then
modify the VBA code.

Some day my heart will stop when I find an easy solution to programming a
minor item like this!

Any and all further ideas you or anyone reading this may have are sincerely
welcome!

Thanks for your reply.

 

[Larry Linson]

First, you _can't_ do a "whole report in VBA"... you can print some things
with PrintOut, but Reports are an entity which can use VBA. CanShrink and
CanGrow are intended just for the kind of situation you describe . . . they
are Properties of a Control, and don't require _ANY_ code. Give CanGrow a
try (and be sure to make sure CanGrow is set for the Report Section in which
the Control is located, too) before rejecting it, or complaining about VBA
and Reports.

Access' Report feature is (at least, arguably) the best Reporting capability
ever included with a desktop database. Some, who already use Microsoft SQL
Server as their back-end datastore, might recommend MS SQL Reporting
Services, instead. I'm not sufficiently familiar with SQL Server Reporting
Services to offer an opinion.

I've seen some impressive work done with Crystal Reports, in a web
environment where Access Reports was not an option. My colleague who did
that work with Crystal said that he preferred to work with Access Reports,
and hadn't found anything in Crystal that he couldn't do with Access.

If you really want, or really need, "fancified formatting", maybe "mail
merge" to Microsoft Word would be an option.

Larry Linson
Microsoft Office Access MVP