Joining fields in creating report-export to word (2002)

[dommerdog]

I have just created a report (membership directory) from my access database. However, I want last name and first name, as well as city and state, all of which are separate fields to be concatenated and separated as follows, for example:

Smith, Fred
Dallas, TX

Also, I exported this to word, figuring I'd have to manually edit these items (for 165 records - not something I look forward to), but I got something that is not in the report data or layout. There is a field entitled "Fax:". It shows up in Word as "Company Fax:" I've gone all over my report setup in Access, and I've checked my autocorrect in Word, but I can't find any reason for this.

If I can join those fields in Access, then my word question is moot; but if I can't, then I've got to get rid of the extraneous fieldname info in word.

Finally, I was able to draw a line beneath each record in Access, but that line gets dropped when I export to word. Any way to get it to stay?

Thank you!

[charlotte]

I'll answer the last question first, because the answer is no. When you publish an Access report to Word (RTF format), you lose any graphics in the report, and there isn't any way to avoid it. What you could do would be to set up a Word merge document and use mail merge to incorporate the Access data. That would allow you to put in lines, but it would also require your designing a Word version of your report.

Your "Company Fax" field name may be a field caption in the Access table underlying the report. Check there. When you say you exported it to Word, are you exporting the query behind the report or publishing the report itself. If it's the latter, you can't make it export something that isn't there. If it's the query that you're exporting, just create a calculated field in the query and concatenate the name fields something like this:

[lastname] & ", " & [firstname]

[dommerdog]

Charlotte, thank you for your response. For the label "Fax" in my access report, I right clicked the label, went to properties, and edited the label to be "Fax". It was originally "Work Fax". What would I do to find out if "Company" is a field caption? It does not appear in the Access report, and in Word, it appears as Company with a tab between it and Fax.

In my export to word, I'm assuming I'm publishing the report. I'm clicking the "W" icon on the toolbar, and the report pops up in Word.

It's rather obvious I'm a novice with reports but have a little more experience with queries. However, I don't know how to do the concatenation you described (I tend to work more with numeric data so export to Excel when I want to use that function, but here I am needing to do it with text in a report). I'd really appreciate your letting me know if it's not too much trouble.

Thank you again.

Rachel

[charlotte]

What is the report based on, a table or a query? If you're getting Company Fax in the published report in Word, then it has to be in the report somewhere. If there is a non-printing character between Company and Fax in the exported report, then you may have some extraneous words in the label for that control that are truncated in Access but get exported to Word. Select the label in the design view of the report and bring up its properties sheet. Go to the Format tab and click in the caption, then hit Shift + F2 to zoom the contents of the property. That will display everything that's in the caption and you can see if there are any words you missed. To find a field caption, open the table itself in design view and examine the properties of the field.

Also keep in mind that controls you hide in the Access report by turning off their visible property can return to haunt you when you publish the report to Word. WYSIWYG isn't exactly what you get when you save an Access report to RTF format.

If you want the name concatenated in the published report, why not just concatenate it in the Access report? You didn't answer the question about whether the report was based on a table or on a query, so I can't really tell you how to do it.

[dommerdog]

Charlotte, thank you again. My report is based on a query. I'll check the properties of the fieldname in the table when I get home from work this evening.

[dommerdog]

Charlotte, I did as you said, and the "Zoom" window contains absolutely nothing! I also opened the table and tried to see what you were telling me to look for in field properties, but when I right click the label, the entire column is selected, and properties is not among the items that displays in the list that opens. I also went back to look at the source of my table - an Excel Worksheet. The fieldname in Excel is "Fax" (without the quotes), regardless of what I might have said previously about what the fieldname is.

I appreciate your help and look forward to hearing back from you.

Rachel

[dommerdog]

Sorry, Charlotte. I responded to myself. If I knew how to copy my response to myself which was intended to be to you into this reply, I'd do it. Since I last wrote, I've gone into access help searching for concatenate but can't find anything relevant (I have only one table in this query, so there are no joins.

Sigh... It all went so well so fast, and now I'm spending days just trying to make it look right so I can deliver it to the printer to be published...

Bummer.

[dommerdog]

I have some good news! I've gotten rid of "Company". I still need help with concatenation.

Here's the deal with Company. It was not a problem with the "Fax" field, it was a problem with another field, "Company Address". The report's laid out in two columns - the leftmost with the member's name and professional information and the rightmost with personal information. There are no labels associated with the data in the leftmost column but there are labels associated with data in the rightmost column. Sounds weird but looks really good.

I notice the field to the left of Fax was Company Address. The fieldwidth edge (I know there's a term for this, I don't know what it is) aligned immediately to the left of the Fax label. When I narrowed the width of Company Address field and published again in word, I saw that "Company" was associated with the Company address field, not the fax field. I did everything you said I should do to the fax field to the Company Address field and found nothing, so in frustration I deleted Company address from the report and dragged it back in. Voila! This time I published to word and that pesky word was gone!

So, if you or somebody would explain how to concatenate Last Name/First Name (I saw your instructions but don't have a clue where to type that stuff in) and city/state, I'd be eternally grateful.

Thank you!

Rachel

[charlotte]

You create a calculated field in the query your report is based on by typing the expression in the line of the query grid where you would ordinarily put the field name. To give it a user-friendly expression name that will show up in the fields list, you do something like this:

FullName: [LastName] & ", " & [FirstName]

or

CityState: [City] & ", " & [State]

Then in your report, instead of dragging the individual city and state or firstname and last name fields onto the report in design view, you drag the calculated fields FullName and CityState.

Does that answer your question?

[dommerdog]

Woo hoo! It worked! At least in the query. I can't get those Expression/fieldnames to show up in the fieldnames in "reports", though. I saved the new query. If you could please tell me how to get those to show up and how to get my data to align bottom in the report, I'd be out of your hair really fast (and eternally grateful).

Thanx!

Rachel

[charlotte]

What do you mean, you can't get them to show up in fieldnames in reports? Is that query the data source for your report? If it is and you've saved it with the calculated fields in it, then when you open the report in design view, the field list *has* to include FullName and CityState. Are we talking about the same thing? The field list can be made visible by clicking the icon on the View menu or by selecting it from the Report Design toolbar if you have it visible.

[dommerdog]

The existing report, when I showed the fieldnames, didn't have my expressions as fieldnames but instead had the old fieldnames (despite my having saved the new query after running it before going back to edit the report). Don't know what happened, but I whipped up a new report, and the wizard brought in the query with the correct fieldnames. I don't know why, but I've got what I need.

We're talking about the same thing - for some reason, something burped somewhere along the way, but it all worked out.

Thank you,

Rachel