Access Query mail merge to Word

[Jan Collier]

I have an Access Query to mail merge with Word and all is well except for the UK post code. i.e PO25 4AE appears PO254AE in the merged document. It appears correctly in the database I'm using input mask >LL99" "0LL;0;

I think I need a switch on the postal code in mailmerge to enforce the space required.

Would very much appreciate some help as I have a mailshot tomorrow and I've spent 2 days trying to sort this out



Thanks

[RuralGuy]

Post the relevant portion of the query so we can look at it. There is no "Input Mask" in a query so to what are you referring?

[NTC]

as the term 'mask' implies - it is an (or can be) artificial presentation and what is actually stored differs (depending on how one sets up the mask).

in the query that is being used as the data source for the Word merge - make 2 new fields (calculated fields)... use the Left Right method....and have one new field be the Left character count of your zip column....and the second column be the Right character count. If you are not familiar with Left Right methods - you will find these methods explained in VB help. There is also Trim and Mid and other interesting text manipulation techniques that are good to become acquainted with - you don't need them now but as one learns to do custom things it is good to know they exist. Give those new fields a meaningful name.

Then in your Word merge use these 2 new query fields - rather than the original so that you can space them out the way you want.

[Jan Collier]

Sorry I don't know how to copy the relevant part of the query.

The input mask is on the table not the query

under the field postal code the input mask is >LL99* *0LL;0;_
Should I also put something into the format section so that word can carry the format over. But I don't know what format to put in. I tried ****#*** but that didn't do the trick

Really appreciate you trying to help me. The database is encrypted and confidential so I will make a short replica and attach it.

Thanks again

Jan

[RuralGuy]

NTC has a good idea but I lean toward a User Defined Function (UDF) myself. A better approach (IMHO) would be to *not* use a mask but request those fields be entered with the appropriate spaces.

[Jan Collier]

I'm obviously very new to this and I think the idea of taking the mask out altogether will be my answer even though I will have to go through all the data and add the relevant space. I will try this and let you know how I get on.

Thanks again must go now will sign on again later to let you know how it went.

[DaveT]

See http://office.microsoft.com/en-us/ac...010096452.aspx

especially the "THREE PARTS OF AN INPUT MASK". Part 2 sets how the data is actually stored in the database. This controls - in your case - whether the data is stored as xxx xxx or xxxxxx.

Anyway, it sounds like your data is stored as xxxxxx (no spaces).

You can fix this at the query level by creating a column such as:

myPostalCode: Left([PostalCode], 3) & " " & Right([PostalCode],3)

[Jan Collier]

Thank you everyone. I have decided to take the easy option because of my deadline tomorrow but I will look at all the other suggestions with a view to improving my knowledge of Access. I have taken the input mask off and put the space into the table manually.

Thanks again.

[RuralGuy]

Jan, are you ready to follow the link in my sig and mark this thread as Solved?