Don't want to wrap long text - just truncate

[Sydney Lotterby]

Word 2003, SqlServer 2000 ... I am trying to print a bunch of labels (0.5" x
1.75") in a TrueType font. One of the merge fields sometimes exceeds the
width of the label and wraps. I would like to truncate it and replace the
characters with an ellipsis (...) to show something was trunc'd. How can I
do this? I can't truncate the text in the Sql select b/c I am using a
TrueType font so don't know how much space the line will actually take.

Also, the data source is a SqlServer table and I would like to be able to
write the sql script to build the source table. I couldn't find anywhere to
do that -- just a wizard that allowed me to select fields. Can this be
done?

tia

 

[Peter Jamieson]

I think the only way you will be able to do this is either to merge to a new
document then postprocess the results using VBA, or use VBA and the
MailMerge events . I don't have code for that - you would probably have to
use Win32 calls to get the text width in a specified font then iterate
somehow until you got a width that would fit.

Also, the data source is a SqlServer table and I would like to be able to
write the sql script to build the source table. I couldn't find anywhere
to do that -- just a wizard that allowed me to select fields. Can this be

All you can do in the standard user interface is specify a table of view,
and specify a bit of sorting and filtering (go into the Edit Recipients box,
click on the column heading of one of the columns, and select the
Advanced... option). To do more, you either have to go through MS Query, or
you have to specify the SQL in an OpenDataSource method call in VBA. To go
via MS Query, MS Query has to be installed, and you will need an ODBC DSN
for your data source (MS Query only works with ODBC, not OLEDB). When you
get to the Select Data Source dialog box, click on the Tools menu in the top
right corner and select MS Query, then follow its dialogs. Be preapred for
failure - it's sometimes quite hard to get MS Query to work with SQL Server
especially if you are not using Integrated Security.

If you go the VBA route, you will need to specify a .udl or .odc file in the
OpenDataSource statement. If you have a working .odc I would use that. Then
specify the SQL in the SQLStatement and SQLStatement1 parameters - Word
concatenates the two values to give the complete SQL statement. It does not
add any white space between the two values when it does that. If you need a
long statement, be careful because there is a limit of either around 256 or
512 characters (it depends...). You may also find that syntactically correct
SQL does not always work - I have found for example that you usually need to
specify and use table aliases even where they are not strictly necessary.

Peter Jamieson

 

[Sydney Lotterby]

Thanks Peter.

I think the only way you will be able to do this is either to merge to a
new document then postprocess the results using VBA, or use VBA and the
MailMerge events . I don't have code for that - you would probably have to
use Win32 calls to get the text width in a specified font then iterate
somehow until you got a width that would fit.


All you can do in the standard user interface is specify a table of view,
and specify a bit of sorting and filtering (go into the Edit Recipients
box, click on the column heading of one of the columns, and select the
Advanced... option). To do more, you either have to go through MS Query,
or you have to specify the SQL in an OpenDataSource method call in VBA. To
go via MS Query, MS Query has to be installed, and you will need an ODBC
DSN for your data source (MS Query only works with ODBC, not OLEDB). When
you get to the Select Data Source dialog box, click on the Tools menu in
the top right corner and select MS Query, then follow its dialogs. Be
preapred for failure - it's sometimes quite hard to get MS Query to work
with SQL Server especially if you are not using Integrated Security.

If you go the VBA route, you will need to specify a .udl or .odc file in
the OpenDataSource statement. If you have a working .odc I would use that.
Then specify the SQL in the SQLStatement and SQLStatement1 parameters -
Word concatenates the two values to give the complete SQL statement. It
does not add any white space between the two values when it does that. If
you need a long statement, be careful because there is a limit of either
around 256 or 512 characters (it depends...). You may also find that
syntactically correct SQL does not always work - I have found for example
that you usually need to specify and use table aliases even where they are
not strictly necessary.

Peter Jamieson