Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Jamie Steenberge Jamie Steenberge is offline
external usenet poster
 
Posts: 1
Default Access table to Mail Merge

I have a table with this information:

StudentID | LastName | FirstName | Grade | Subjects
103289 | Doe | Jane | 7 |
MathChr(9)43Chr(9)1,2,5Chr(13)Chr(10)ScienceChr(9) 61Chr(9)3,4
64897 | Does | John | 6 |
ArtChr(9)43Chr(9)1,2,5Chr(13)Chr(10)HistoryChr(9)6 1Chr(9)3EnglishChr(9)64Chr(9)4,8
54678 | Doer | Jill | 8 |
EnglishChr(9)38Chr(9)1

I need to know two things.

1 - Each row should be on it's own page

2 - The Subjects column is tab delimited, and if there are multiple records
there are return characters at the end of the preceding lines. I could make
it whatever for the delimiters though. I need to be able to place the
Subjects column in a table on the report.

How can this be done?

Thanks in advance,

Jamie


  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Access table to Mail Merge

Assuming I have understood correctly and
a. your Subjects column is actually a memo field in Access and
b. what you want is something like

StudentID: 103289
LastName: Doe
FirstName: Jane
Grade: 7

-------------------------
| Subject | % | XXX |
-------------------------
| Math | 43 | 1,2,5 |
-------------------------
| Science | 61 | 3,4 |
-------------------------

next page

StudentID: 64897
etc.

where | and - represent real table cell borders, then I think your best
bet is probably to do the merge to a new document, inserting your
Subject field content as is, but surrounded by recognisable markers and
any trim text that needs to go in the table, e.g. so you have something like

+++
Subjecttab%tabXXXcrlf
Mathtab43tab1,2,5crlf
Sciencetab61tab3,4
---

then use a VBA macro to select each chunk of text between +++ and ---
and use ConvertTextToTable to delete the +++ and ---, convert the text
to a table, and format it how you want.

e.g.

Sub Macro1()
Dim rngSearch As Word.Range
Dim tblCurrent As Word.Table
Set rngSearch = ActiveDocument.Content
With rngSearch
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "(+++)(*)(---)"
.Replacement.Text = "\2"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchKashida = False
.MatchDiacritics = False
.MatchAlefHamza = False
.MatchControl = False
.MatchByte = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
.Find.Execute Replace:=True
While .Find.Found
Set tblCurrent = .ConvertToTable(Separator:=vbTab)
With tblCurrent
' do any table formatting in here, e.g.
.Borders.Enable = True
.Columns(1).Width = 100
End With
Set tblCurrent = Nothing
' then find the next chunk
rngSearch.SetRange _
Start:=rngSearch.End, _
End:=ActiveDocument.Content.End
.Find.Execute Replace:=True
Wend
End With
End Sub

See, e.g. Graham Mayor's article at

http://www.gmayor.com/installing_macro.htm

for hints on how to install macros.

There are other ways to do this, but if that looks as if it does the
basics, I suspect it's the simplest approach in this case.


Peter Jamieson

http://tips.pjmsn.me.uk

Jamie Steenberge wrote:
I have a table with this information:

StudentID | LastName | FirstName | Grade | Subjects
103289 | Doe | Jane | 7 |
MathChr(9)43Chr(9)1,2,5Chr(13)Chr(10)ScienceChr(9) 61Chr(9)3,4
64897 | Does | John | 6 |
ArtChr(9)43Chr(9)1,2,5Chr(13)Chr(10)HistoryChr(9)6 1Chr(9)3EnglishChr(9)64Chr(9)4,8
54678 | Doer | Jill | 8 |
EnglishChr(9)38Chr(9)1

I need to know two things.

1 - Each row should be on it's own page

2 - The Subjects column is tab delimited, and if there are multiple records
there are return characters at the end of the preceding lines. I could make
it whatever for the delimiters though. I need to be able to place the
Subjects column in a table on the report.

How can this be done?

Thanks in advance,

Jamie


Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge does not include all records from list or Access table Paul Fahey Mailmerge 0 March 27th 06 12:08 AM
Mail Merge does not include all records from list or Access table Doug Robbins - Word MVP Mailmerge 0 March 26th 06 11:54 PM
Mail Merge from an Access Database table iholder Mailmerge 1 March 18th 06 12:57 PM
Can Word 2003 mail merge an Access value from a look-up table? Marge Mailmerge 1 January 17th 06 05:59 AM
Access to Word Table Mail Merge Tammi Mailmerge 6 November 7th 05 07:40 PM


All times are GMT +1. The time now is 05:58 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"