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

Doug,

I still get the Syntax error message on the SQL statement, btw.

    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

Cheers

N.

Answer 3

Thanks folks.  Hans, I like the way you think. The extra field is a happening thing.  Means that I don't lose the original values and can check my own work.

Cheers

N.

Answer 4

Try changing

"WHERE tblAirports.Country = rs!CountryNAME"

to

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

But I'd do it differently:

Add a number field CountryID to tblAirports.

Create an update query

UDATE tblAirports INNER JOIN tblCountry ON tblAirports.Country = tblCountry.CountryName

SET tblAirports.CountryID = tblCountry.CountryID

You can delete the Country field from tblAirports after successfully running the query.

Answer 5

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

            rs!CountryNAME & "','" & _

            rs!CountryID & "') " & _

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

---

Doug Steele, Microsoft Access MVP

http://www.AccessMVP.com/djsteele (no e-mails, please!)

Co-author, Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (published by Wiley, ISBN 978-0-470-59168-0)