Easy formating

[Big X]

Hello all.

I have a column heading VIN with data like

MWN4567875PO456789

What I need to do is a mail merge in word to create envelopes. I have the data in access. On the letter however need to print the last 10 values. Is there a easy way in SQL or access to create a column or crop that column to turn the values into

75PO456789

Even if its a solution in word to change it, it would be good. I tried changing field length to 10 in design view but it gave me the first 10 values and i need the last ten.

[Stewart Ross]

Hi. I am assuming that you are accessing an Access Query from Word and not just the base table concerned. This is important because there is a very simple function,

Right([string value], [N characters])

which you can use in an Access query to extract the ten right-most characters, but it cannot be used directly in the base table itself.

Its use involves creating a calculated field in the query editor, like this:

VINPart: Right([VIN], 10)

where VINPart would be the name of the calculated field. You would need to replace [VIN] with the actual name of the field concerned from your base table.

Although Word can be programmed to do the trimming of the merge field itself as far as I know it would need to be done from VBA code, by programming the current manual merge, and this is a much bigger step than simply using Right() in an Access query.

If you do need to access a base table for the merge you can always export the query to Excel and merge from that datasource instead, or to another (temporary) Access table instead.

-Stewart