SQL Error

[fogal]

I have an sql statement I run to append records into a recordset. It works fine when [Forms]![frmAdvertQueries]![RenewFrom] has a value(i.e number), but fails when this value is a null value. When using a null value I get the error message:
Extra ) in query expression '(((tblAdverts.Publid)=38) And ((tblAdverts_1.PublID)=))'

strsql2 = "SELECT tblAdvDuplicates.AdvertID, 1 AS AlreadyDuplicate, " & Forms!frmUserCapture!User & " AS Operator FROM (tblAdvDuplicates INNER JOIN tblAdverts ON tblAdvDuplicates.DuplID = tblAdverts.AdvertID) INNER JOIN tblAdverts AS tblAdverts_1 ON tblAdvDuplicates.AdvertID = tblAdverts_1.AdvertID WHERE (((tblAdverts.PublID) = " & [Forms]![frmAdvertQueries]![PublicationID] & &quot And ((tblAdverts_1.PublID) = " & [Forms]![frmAdvertQueries]![RenewFrom] & &quot)GROUP BY tblAdvDuplicates.AdvertID;"

 

[JeffTullin]

if there is any chance that your source will be null, you should be wrapping that value inside an IIF function.

eg instead of
myform.mytextbox
you should have
iif(myform.mytextbox is null,'',myform.mytextbox)

 

[fogal]

Just tried that and it produced the same error message.

I've now tried the following and it works:
If IsNull([Forms]![frmAdvertQueries]![RenewFrom]) Then
strsql2 = "SELECT tblAdvDuplicates.AdvertID, 1 AS AlreadyDuplicate, " & Forms!frmUserCapture!User & " AS Operator FROM (tblAdvDuplicates INNER JOIN tblAdverts ON tblAdvDuplicates.DuplID = tblAdverts.AdvertID) INNER JOIN tblAdverts AS tblAdverts_1 ON tblAdvDuplicates.AdvertID = tblAdverts_1.AdvertID WHERE (((tblAdverts.PublID) = " & [Forms]![frmAdvertQueries]![PublicationID] & &quot And ((tblAdverts_1.PublID) = null))GROUP BY tblAdvDuplicates.AdvertID;"
Else
strsql2 = "SELECT tblAdvDuplicates.AdvertID, 1 AS AlreadyDuplicate, " & Forms!frmUserCapture!User & " AS Operator FROM (tblAdvDuplicates INNER JOIN tblAdverts ON tblAdvDuplicates.DuplID = tblAdverts.AdvertID) INNER JOIN tblAdverts AS tblAdverts_1 ON tblAdvDuplicates.AdvertID = tblAdverts_1.AdvertID WHERE (((tblAdverts.PublID) = " & [Forms]![frmAdvertQueries]![PublicationID] & &quot And ((tblAdverts_1.PublID) = " & [Forms]![frmAdvertQueries]![RenewFrom] & &quot)GROUP BY tblAdvDuplicates.AdvertID;"
End If