Missing operator in query and need suggestion for retrieving records 2

[CmptrTk]

Left
Aligned Text

Here's the code that produces the missing operator error:
Do Until rs_sorted.BOF
mysql = " UPDATE PBillImportTemp " & _
" SET PBillImportTemp.Extension = tblimportExt.Extension " & _
[highlight #FF99FF]

" WHERE ((PBillImportTemp.Date <> ' Extension ' ) " & _
" AND (PBillImportTemp.Extension Is Null)) " & _
" OR [/highlight]HAVING PBillImportTemp.Extension Not Like 'Wp*'"
DoCmd.RunSQL mysql

***********************************************************

I'm also experiencing problems advancing through the retrieved records. Here's what I've done:

Set rs_sorted = db.OpenRecordset("qryPBillImportTemp_allExts")
Set rs1 = db.OpenRecordset("qrytblImportExt")
rs_sorted.MoveLast 'because this isn't a table you have to use
'the .movelast method to get all the rows

If rs_sorted.RecordCount > 0 Then

rs_sorted.MovePrevious
If rs1.BOF Then
rs1.MoveFirst
Else: rs1.MoveNext
End If
If rs_sorted.Fields("[Extension]").Value = rs1.Fields("[Extension]").Value Then
rs_sorted.MovePrevious 'move backwards through the recordset
Else
Do Until rs_sorted.BOF

(the previously mentioned "update" query is here)


rs_sorted.MovePrevious 'now fill the next blank row with the value from the
'tblImportExt

Loop
End If
rs_sorted.MovePrevious
End If

Set rs_sorted = Nothing
Set rs1 = Nothing
Set db = Nothing



​

Any suggestions would be appreciated.

 

[Zion7]

What I've noticed, offhand is, your missing a semi colon at the end of your SQL statement.

 

[PHV]

The syntax of your UPDATE statement is quite wrong:
1) the HAVING clause is illegal in this context (allowed only after a GROUP BY clause)
2) reference to tblimportExt.Extension must be done in a subquery.
Try something like this:
mysql = _
"UPDATE PBillImportTemp SET PBillImportTemp.Extension=" & _
"(SELECT Extension FROM tblimportExt" & _
" WHERE tblimportExt.SomeKeyField=PBillImportTemp.SomeKeyField)" & _
" WHERE PBillImportTemp.Date<>' Extension '" & _
" AND Nz(PBillImportTemp.Extension) Not Like 'Wp*';"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244