SQL Syntax error 3144

Question

Arghh !!! I'm having trouble debugging this SQL statement.

Private Sub ReplaceAirportNames_Click()

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim strSQL As String

    strSQL = "SELECT CountryID, CountryName FROM tblCountry"

    Set db = CurrentDb

    Set rs = db.OpenRecordset(strSQL)

    Do While rs.EOF = False

        strSQL = "UPDATE tblAirports" & _

            "SET tblAirports.Country = Replace([Country],'" & _

            rs!CountryNAME & "','" & _

            rs!CountryID & "') " & _

            "WHERE tblAirports.Country = rs!CountryNAME"

        db.Execute strSQL, dbFailOnError

        rs.MoveNext

    Loop

    rs.Close

    Set rs = Nothing

    Set db = Nothing

End Sub

I get Syntax error 3144.

I THINK the problem is in the "WHERE ... " part of the statement as I never seem to get this right.

What it is supposed to do: tblCountry has CountryID and CountryName.  tblAirports has text field, Country.  Basically, I want to turn this into a field that references the tblCountry.CountryID field by replacing the text string in tblAirports.Country with the numeric value of tblCountry.CountryID.

tblCountry has around 200 records but tblAirports has over 9000.  I've downloaded both datasets from the internet and imported them into Access.

All help appreciated.

Answer 1

Blanks are meaningful. Your concatenated string is going to be

UPDATE tblAirportsSET tblAirports.Country = Replace([Country], <etc>

You need a blank before the SET operator:

strSQL = "UPDATE tblAirports " & _

            "SET tblAirports.Country = Replace([Country],'" & _

            rs!CountryNAME & "','" & _

            rs!CountryID & "') " & _

            "WHERE tblAirports.Country = '" & rs!CountryNAME & "'"

---

John W. Vinson/MVP

Answer 2

Come to think of it. Why would anyone change or replace the field/coulmn "tblAirports.Country" to the "CountryID"?

I mean "CountryID" is a Index or a Primary key. They are different data as the WHERE condition indicates tblAirports.Country = '" & rs!CountryName &"'" (tblCountry.CountryName). Unless tblAirports.Country is in fact an ID.

---

Please Mark As Answered if it solved the problem.