Hi people,
Hope you can help me with a problem, it's rather a simple one and I apologise for my ignorance.

Ok a problem with defining a select query for a mail merge.
I want to have a pop-up box and then use this value as the query data for a mail merge and have written the following quick macro:

Sub test_mail_merge()
'
' test_mail_merge Macro
' Macro by JPC
'
Dim payno As Integer
payno = InputBox("payroll number", "Payroll Selection", 0)

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM C:\Documents and Settings\client\Desktop\test merge data.csv WHERE (Payroll = payno)"

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
End Sub

IT seems to be using the variable as a literal value, if i replace the "payno" variable in the SELECT query with a real value the merge works fine and only merges the one record, i'm obviously using the defined variable "payno" incorrectly, have tried removing brackets ect but no luck, any ideas.
Cheers
JP

Hi JP

You are right that payno is being used as a literal value. You simply need to build up the query in parts. So instead use:

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM C:\Documents and Settings\client\Desktop\test merge data.csv WHERE (Payroll = " & CStr(payno) & ")"

In case you dont know, & joins strings together. You need the CStr function to convert payno to a string, as string & number doesn't make sense.

Hope this helps
Philip Cole