Syntax Error

[primagic]

I am getting a Syntax Error (missing operator) in query expression '( <> NULL) AND'.

This is my code:

[code]
Private Sub btnTestPlainEmail_Click()
On Error GoTo btnTestPlainEmail_Click_error


Dim s As String
Dim recipient As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord

If Nz(Me![MailMergeEmailSubjectLine], "") = "" Then
MsgBox "You need to enter an email subject line for me to use", vbInformation + vbOKOnly, "Cancelled"
GoTo btnTestPlainEmail_Click_Exit
End If

If Nz(Me![MailMergeEmailMessage], "") = "" Then
MsgBox "You need to enter an email message for me to send", vbInformation + vbOKOnly, "Cancelled"
GoTo btnTestPlainEmail_Click_Exit
End If

s = MakeFilter
If s = "Error" Then
GoTo btnTestPlainEmail_Click_Exit
End If

s = "SELECT TOP 1 * FROM [qryCampaignRecipients] WHERE ([Email] <> NULL) AND " & s
Set rs = db.OpenRecordset(s, dbOpenSnapshot)
If rs.EOF Then
MsgBox "There are no contacts with email addresses that matched your criteria", vbInformation + vbOKOnly, "No Data"
GoTo btnTestPlainEmail_Click_Exit
End If

s = rs![Salutation] & vbNewLine & vbNewLine & rs![MailMergeEmailMessage]

recipient = rs![Email1]
If Nz(Me![MailMergeccEmail2?], False) Then
If Nz(rs![Email2], "") <> "" Then
recipient = recipient & "; " & rs![Email2]
End If
End If
SendMail recipient, Nz(rs![MailMergeEmailSubjectLine], rs![MailMergeID]), s, True, Me![MailMergeEmailFileAttachment], False


btnTestPlainEmail_Click_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub

btnTestPlainEmail_Click_error:
Select Case Err
Case 2501 'action cancelled
Resume btnTestPlainEmail_Click_Exit
Case Else
MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, "Error in module btnTestPlainEmail_Click"
Resume btnTestPlainEmail_Click_Exit
End Select
End Sub
[/code]

The error appears on this line:

[code]
Set rs = db.OpenRecordset(s, dbOpenSnapshot)
[/code]

Help please. Where am i going wrong?

 

[MajP]

not Email Is NULL
is the correct syntax

 

[PHV]

WHERE ( IS NOT NULL) AND

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[primagic]

I replace the code with:

s = "SELECT TOP 1 * FROM [qryCampaignRecipients] WHERE ([Email] IS NOT NULL) AND " & s

But still get the same error

 

[traingamer]

s = "SELECT TOP 1 * FROM [qryCampaignRecipients] WHERE ([Email] IS NOT NULL) AND " & [blue]s[/blue]

What is MakeFilter?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard