Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Run time Error "3144"

    Syntax error in UPDATE Statement.

    Getting dizzy looking at this trying to figure out what's wrong.

    Any ideas?

    Code:
    Private Sub Command81_Click()
    Dim strSQL As String
    strSQL = "UPDATE tblCARETS-Reports INNER JOIN City-Area-List ON tblCARETS-Reports.City = City-Area-List.CityName Set tblCARETS-Reports.City = [City-Area-List].[CityID]"
    CurrentDb.Execute strSQL
    MsgBox "FINISHED NORMALIZING CITY CODES RICK"
    End Sub
    Thanks . . . Rick

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    The error statement is on the "CurrentDb.Execute strSQL" line.

    Sorry . . .

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For starters, because of the inadvisable symbol in your names, you'd have to bracket them.
    Paul

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    strSQL = "UPDATE tblCARETS-Reports INNER JOIN City-Area-List ON tblCARETS-Reports.City = City-Area-List.CityName Set tblCARETS-Reports.City = [City-Area-List].[CityID]"
    CurrentDb.Execute strSQL
    MsgBox "FINISHED NORMALIZING CITY CODES RICK"
    End Sub

    should be...


    strSQL = "UPDATE [tblCARETS-Reports] INNER JOIN [City-Area-List] ON [tblCARETS-Reports].City = [City-Area-List].CityName Set [tblCARETS-Reports].City = [City-Area-List].[CityID]"
    CurrentDb.Execute strSQL
    MsgBox "FINISHED NORMALIZING CITY CODES RICK"
    End Sub

    - in a field/tablename are not advisable (thinks it needs to do an equation!)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    pkStormy - that did the trick first try.

    Can't thank you enough.

    Thanks much.

    Rick

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Run Time Error: "3144" Syntax Error in Update Statement

    This is the line that it occurs . . . on the first occourance of

    CurrentDb.Execute strSQL

    I removed the - from the table and field names.

    Code:
    Private Sub Command84_Click()
    Dim strSQL As String
    strSQL = "UPDATE [tblCARETSReports] INNER JOIN [TblCARETSNormOfcnme] ON [tblCARETSReports].[ListID] = [TblCARETSNormOfcnme].[UID Set [tblCARETSReports].[ListName] = [tblCARETSNormOfcnme].[Officename]"
    CurrentDb.Execute strSQL
    strSQL = "UPDATE [tblCARETSReports] INNER JOIN [TblCARETSNormOfcnme] ON [tblCARETSReports].[SellID] = [TblCARETSNormOfcnme].[UID] Set [tblCARETSReports].[SellName] = [tblCARETSNormOfcnme].[Officename]"
    CurrentDb.Execute strSQL
    MsgBox "FINISHED NORMALIZING CARETSREPORTS OFFICE NAMES TABLE RICK"
    End Sub

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If that's a copy/paste, you're missing a bracket after UID.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •