Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query code (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query code (2003)

    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"

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query code (2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query code (2003)

    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)

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query code (2003)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query code (2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •