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

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

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