Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two fields in Access, FN and LN that need to be converted to word without being separated by anything except a space. For instance, John in FN and Doe in LN should appear as John Doe, rather than 2 columns. However it goes beyond that. I have about 700 names like that that need to appear that way in word, but between each full name, I need a comma, and then a continued string. In other words, if I have columns like this:

John     Doe
Jane     Doe
Chris    Doe
Amy      Doe

They need to end up like this:

John Doe, Jane Doe, Chris Doe, Amy Doe

They need to wrap if the string gets too long because these need to go in a report. Could even possibly be done in Excel and copied to Word, but the end result is this needs to be in a text format.

Any suggestions? Thanks for any help you can give. Please note: I don't know VB so if the solution involves VB I would need very simple step by step instructions.

Also, is there any way to automate this process? I need to create this whole process for an end user so that it automatically does it per her request rather than her having to learn how to use Access, Excel, Word... whatever. I create databases with menus so that end users only have to click a menu item to accomplish whatever task they're needing since they know practically nothing about Access or about programming.

share|improve this question

Don't know what version of Word and Access you are using, so specifics may be a bit different.

You can use the Mail Merge feature of Word to create a directory of those names. This will allow you to select which names to import and how to lay it out. There are options to create letters, envelopes, label and directories. You will want the directory option to get them all on the same page.

Open a new document, from the Mailing Tab use select recipients to choose an existing list. This will give you a file open dialog box. Find your database and select the table with the names. Now choose the merge fields dropdown and choose FN to insert it. Do the same for LN with a space between. Add a comma and a space to the end.

enter image description here

Your layout will look like this (with a space after the comma):

<<FN>> <<LN>>,

When finished you should get a list of full names in paragraph format. You can cut and past this into your document.

If you need detailed instructions how to use mail merge, here is a walk through.

share|improve this answer
Thank you so much for your response! I haven't tried it yet because I actually came up with my own solution. I select the records I want in Access and Copy to Word (I'm using Office Pro 2010). I then wrote a macro in Word that pastes the data, then does Find and Replace on the tabs and paragraph marks. Works like a charm and the End User only needs to know how to select the records in Access, Open Word, and run a macro. Now if only I could figure how to do a Copy Selected Records with an Access Macro....sigh....any ideas? Thanks again – Sherry Jan 13 '12 at 22:11
You are welcome, Sherry. Mail merge is a cool tool once you get used to it. As far as Access, you should be able to build a form for them to select the records and export (copy) them. Might even be able to do the whole thing, start to finish, in Access VBA. – CharlieRB Jan 14 '12 at 0:18

I figured this one out for myself. In Access, I selected the records I wanted, then simply did a copy. I also wrote a macro in word (and created a menu button for it) that when clicked, pastes those records into Word, then does some Find and Replace commands and it's done.

So, the End User has to only run the macro I used in Access to open the selected records, then open Word and run the Word macro by clicking the button on the menu bar.

Pretty simple and quick method for the user.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .