Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Address Simulation Report trouble
Helo all,
Could someone help me? I create a Address Simulation Report for all the mailmerges I do. I choose a representative sample of the mailmerge data using the MOD function. I have the following which works as far as it goes. I'd like to choose also the very last record of any data file I attach to. I've tried searching the web but I can't seem to find any more complex examples than just SELECT * FROM XXX. I've also tried several trial and error solutions but I just can't seem to make it work properly. ' open connection to populate RecordCount With Dialogs(wdDialogMailMergeOpenDataSource) .Name = "J:\ML\" & PATH & "\ADR\" & FileName .Execute RecordCount = ActiveDocument.MailMerge.DataSource.RecordCount 'determine total records in the data temp = RecordCount / numLabels ' numLabels = number of address simulations I want to generate Divisor = Int(temp) End With 'end of open data With Dialogs(wdDialogMailMergeOpenDataSource) .Name = "J:\ML\" & PATH & "\ADR\" & FileName ' choose every (pc_no_seg MOD " & Divisor & " = 1) record. pc_no_seg is a sequential number in the data myQuery = "SELECT * FROM " & FileName & " WHERE (pc_no_seg MOD " & Divisor & " = 1)" ' WORKS but I want to select the LAST record also. .SQLStatement = myQuery .Execute End With 'end of open data How would I modify this SQLStatement to include the very last record? Thanx Dale Jones |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Address Simulation Report trouble
This is not necessarily easy to do because
a. SQL is not designed to pick records based on their position in an unordered list b. the SQL constructs available to you depend on the data source being used However, if you know the records will be sequenced in "pc_no_seg" order, with some dialects of SQL you might be able to use something like "SELECT TOP 1 * FROM " & FileName & " ORDER BY pc_no_seg DESC" or if pc_no_seg is in effect a record number you might be able to do something along the lines of "SELECT * FROM " & FileName & " WHERE pc_no_seg = (SELECT count(*) FROM " & FileName & ")" If you can't do anything like that, your best bets are probably a. open the full data source, and use the MailMerge object model in VBA to step through the records until you reach the last one (in my experience, you can't always simply jump to the last record) and stuff the data in that record into your report or b. use another method altogether (e.g. ADO) to open your data source, and stuff the data into your Word document. Peter Jamieson "Dale" D-Man wrote in message ... Helo all, Could someone help me? I create a Address Simulation Report for all the mailmerges I do. I choose a representative sample of the mailmerge data using the MOD function. I have the following which works as far as it goes. I'd like to choose also the very last record of any data file I attach to. I've tried searching the web but I can't seem to find any more complex examples than just SELECT * FROM XXX. I've also tried several trial and error solutions but I just can't seem to make it work properly. ' open connection to populate RecordCount With Dialogs(wdDialogMailMergeOpenDataSource) .Name = "J:\ML\" & PATH & "\ADR\" & FileName .Execute RecordCount = ActiveDocument.MailMerge.DataSource.RecordCount 'determine total records in the data temp = RecordCount / numLabels ' numLabels = number of address simulations I want to generate Divisor = Int(temp) End With 'end of open data With Dialogs(wdDialogMailMergeOpenDataSource) .Name = "J:\ML\" & PATH & "\ADR\" & FileName ' choose every (pc_no_seg MOD " & Divisor & " = 1) record. pc_no_seg is a sequential number in the data myQuery = "SELECT * FROM " & FileName & " WHERE (pc_no_seg MOD " & Divisor & " = 1)" ' WORKS but I want to select the LAST record also. .SQLStatement = myQuery .Execute End With 'end of open data How would I modify this SQLStatement to include the very last record? Thanx Dale Jones |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Address Simulation Report trouble
Thanx so much for your response Peter. Not bieng a good programmer, I tend
to go with whatever I can make to work. Data IS sequenced in "pc_no_seg" order and pc_no_seg IS in effect a record number. I'll let your comments sink in and stew a while. Does anyone else have a suggestion? A refresh/reiteration of my question is "What I have works." I want to continue using it AND choose the last record of any data file I will connect to. "Peter Jamieson" wrote in message ... This is not necessarily easy to do because a. SQL is not designed to pick records based on their position in an unordered list b. the SQL constructs available to you depend on the data source being used However, if you know the records will be sequenced in "pc_no_seg" order, with some dialects of SQL you might be able to use something like "SELECT TOP 1 * FROM " & FileName & " ORDER BY pc_no_seg DESC" or if pc_no_seg is in effect a record number you might be able to do something along the lines of "SELECT * FROM " & FileName & " WHERE pc_no_seg = (SELECT count(*) FROM " & FileName & ")" If you can't do anything like that, your best bets are probably a. open the full data source, and use the MailMerge object model in VBA to step through the records until you reach the last one (in my experience, you can't always simply jump to the last record) and stuff the data in that record into your report or b. use another method altogether (e.g. ADO) to open your data source, and stuff the data into your Word document. Peter Jamieson "Dale" D-Man wrote in message ... Helo all, Could someone help me? I create a Address Simulation Report for all the mailmerges I do. I choose a representative sample of the mailmerge data using the MOD function. I have the following which works as far as it goes. I'd like to choose also the very last record of any data file I attach to. I've tried searching the web but I can't seem to find any more complex examples than just SELECT * FROM XXX. I've also tried several trial and error solutions but I just can't seem to make it work properly. ' open connection to populate RecordCount With Dialogs(wdDialogMailMergeOpenDataSource) .Name = "J:\ML\" & PATH & "\ADR\" & FileName .Execute RecordCount = ActiveDocument.MailMerge.DataSource.RecordCount 'determine total records in the data temp = RecordCount / numLabels ' numLabels = number of address simulations I want to generate Divisor = Int(temp) End With 'end of open data With Dialogs(wdDialogMailMergeOpenDataSource) .Name = "J:\ML\" & PATH & "\ADR\" & FileName ' choose every (pc_no_seg MOD " & Divisor & " = 1) record. pc_no_seg is a sequential number in the data myQuery = "SELECT * FROM " & FileName & " WHERE (pc_no_seg MOD " & Divisor & " = 1)" ' WORKS but I want to select the LAST record also. .SQLStatement = myQuery .Execute End With 'end of open data How would I modify this SQLStatement to include the very last record? Thanx Dale Jones |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Address Button v. Envelopes | Microsoft Word Help | |||
Mail Merge Address Block Missing Address 3 | Mailmerge | |||
Return Address along Left Side of Envelope | Microsoft Word Help | |||
Insert Address from Address Book | Microsoft Word Help | |||
Requirement for address book contact for addressing an envelope? | Microsoft Word Help |