query code (2003)

[nebbia]

I am trying to run a query with this code;
strSQL = "SELECT * FROM qrysignin WHERE name=" & Chr(39) & me.lastname & Chr(39)


Set wrdDoc = wrdApp.Documents.open("C:My DocumentsAccesstemplatese-mailappointmentReminder.doc")
With wrdDoc.MailMerge
.OpenDataSource _
Name:="", _
LinkToSource:=True, _
Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name, _
SQLStatement:=strSQL, _
SubType:=8 ' = wdMergeSubTypeWord2000
.SuppressBlankLines = True
End With

The query SQL is:
SELECT Contacts.First, Contacts.Name, Contacts.[e-mail], tblAppointments.ApptDate, tblAppointments.ApptTime
FROM (Contacts LEFT JOIN Patients ON Contacts.Name = Patients.Name) LEFT JOIN (Visit LEFT JOIN tblAppointments ON Visit.VisitID = tblAppointments.VisitID) ON Patients.PatientID = Visit.PatientID
WHERE (((Contacts.Name)=[forms]![contacts]![lastname]))
ORDER BY Contacts.Name
WITH OWNERACCESS OPTION;

I believe the problem lies with strSQL = "SELECT * FROM qrysignin WHERE name=" & Chr(39) & me.lastname & Chr(39) but I can't find the problem.

Does anyone see the mistake?

nebbia

[HansV]

The problem is the refefence to a form. ODBC doesn't know about Access forms. Change the line

strSQL = "SELECT * FROM qrysignin WHERE name=" & Chr(39) & me.lastname & Chr(39)

to

strSQL = "SELECT Contacts.First, Contacts.Name, Contacts.[e-mail], tblAppointments.ApptDate, tblAppointments.ApptTime " & _
"FROM (Contacts LEFT JOIN Patients ON Contacts.Name = Patients.Name) LEFT JOIN (Visit LEFT JOIN tblAppointments " & _
"ON Visit.VisitID = tblAppointments.VisitID) ON Patients.PatientID = Visit.PatientID " & _
"WHERE Contacts.Name=" & Chr(39) & Me.LastName & Chr(39) & " ORDER BY Contacts.Name WITH OWNERACCESS OPTION"

[nebbia]

I made the change to the code as you had suggested. I am now getting an error 'string is longer than 255 characters. Also can I substitute a reference to me.lastname from another form.

Thanks

[HansV]

Change the definition of qrySignin to

SELECT Contacts.First, Contacts.Name, Contacts.[e-mail], tblAppointments.ApptDate, tblAppointments.ApptTime
FROM (Contacts LEFT JOIN Patients ON Contacts.Name = Patients.Name) LEFT JOIN (Visit LEFT JOIN tblAppointments ON Visit.VisitID = tblAppointments.VisitID) ON Patients.PatientID = Visit.PatientID
ORDER BY Contacts.Name
WITH OWNERACCESS OPTION;

You should now be able to use your original line

strSQL = "SELECT * FROM qrySignin WHERE name=" & Chr(39) & Me.LastName & Chr(39)

You can refer to another form too:

strSQL = "SELECT * FROM qrySignin WHERE name=" & Chr(39) & Forms!SomeOtherForm!LastName & Chr(39)

[nebbia]

The changes in the query design seemed to have made the difference. An unrelated problem seems to have developed. The query returns apptdate and appttime in a standard format. However, on the merge document I get both the date and time for each field e.g. 06/15/2007 12:00AM

Can I format the merge field to read 'date' at 'hour'

As always thanks very much for the help

nebbia

[HansV]

Right-click the apptdate field and select Toggle Field Codes from the popup menu.
You'll see { MERGEFIELD apptdate } or { MERGEFIELD apptdate * MERGEFORMAT }
Insert the following format string after the field name, but before * MERGEFORMAT if present:
<code>
@ "MMMM d, yyyy"
</code>
Press F9 to hide the field code and update the result.
Do the same for the appttime field, but use the format
<code>
@ "h:mm AM/PM"
</code>
You can change the format strings to suit your preferences.