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

You're right. The Replace function is referring to the recordset rs, and the Jet Engine that's running the SQL knows nothing about recordsets.

Now that I've looked at the statement more closely, it should be

strSQL = "UPDATE tblAirports " & _

            "SET tblAirports.Country = '" & rs!CountryID & "' " & _

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

On the other hand, using a recordset isn't the best way. You should be able to do it using a single Update statement.

UPDATE tblAirport SET Country = C.CountryID FROM tblAirport A, tblCountry C WHERE A.Country = C.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)

Answer 3

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

Nigel and John,

I think the remaining syntax error is here. The Replace function cannot be is the SQL String.

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

            rs!CountryNAME & "','" & _

            rs!CountryID & "') " & _

Change it to

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

There could be error in the function expression or the SQL string quotes. Let us know if it works.

---

Please Mark As Answered if it solved the problem.

Answer 4

Actually, I'm not sure the quotes are necessary in the call to the Replace function.

strSQL = "UPDATE tblAirports " & _

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

            rs!CountryNAME & "," & _

            rs!CountryID & ") " & _

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

With them, the function's going to be looking for, say, 'USA' when in fact it's only USA.

---

---

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)

Answer 5

It seems I'm still having no joy.  The contents of tblAirports.Country is in upper case.  The contents of tblCountry.CountryName is capitalised.  Could this be part of the problem?