Access SQL Error

[D Willett]

Hey Guys
I'm getting syntax error in the following SQL Insert Into statement.
Its a routine to copy over a set of records from a form into another table (tblAddresses). I hate SQL !!!!!, can any of you guys assist?

Code: Select all

            If Me.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [tblAddresses] (CustomerID, Company, Last Name, First Name, E-mail Address, Job Title, Business Phone, Home Phone, Mobile Phone, Fax Number, Address, City, County, Postal Code, Notes) " & _
                    "SELECT CustomerID, Company, Last Name, First Name, E-mail Address, Job Title, Business Phone, Home Phone, Mobile Phone, Fax Number, Address, City, County, Postal Code, Notes" & _
                    "FROM [tblCustomers] WHERE CustomerID = " & Me.CustomerID & ";"
                CurrentDb.Execute strSql, dbFailOnError
            End If

Kind Regards

[HansV]

Field names that contain spaces or punctuation must be enclosed in square brackets [ ]. And you forgot the space between Notes and FROM.

Code: Select all

                strSql = "INSERT INTO [tblAddresses] (CustomerID, Company, [Last Name], [First Name], " & _
                    "[E-mail Address], [Job Title], [Business Phone], [Home Phone], [Mobile Phone], " & _
                    "[Fax Number], Address, City, County, [Postal Code], Notes) " & _
                    "SELECT CustomerID, Company, [Last Name], [First Name], [E-mail Address], " & _
                    "[Job Title], [Business Phone], [Home Phone], [Mobile Phone], [Fax Number], " & _
                    "Address, City, County, [Postal Code], Notes " & _
                    "FROM [tblCustomers] WHERE CustomerID = " & Me.CustomerID & ";"

[Mark L]

As Hans said, you need to use brackets with non-conforming object names.

I'd suggest you stick to using letters, numbers, and the underscore character when naming objects. It will make your life a lot easier in the long run; and it will require a lot less typing (always a big plus for me)!

I'd also suggest that you learn to embrace SQL. When I first learned Access, I thought the same thing. Who needs it? After all, that's what the query grid is for. But I quickly learned otherwise! If your intent is to be a real Access developer, you have to know SQL.

[D Willett]

Thank you guys.
I should have known about the spaces !! SQL has always defeated me, perhaps because of no intellisense for a guide.
The table in question has been inherited so I'm tasked with trying to tidy it up, my usual naming convention contains no spaces on fields in the table design which I've learned over the years by some catastrophic errors..
Suppose we've all been there..

Thank you again for the help and support guys, its very much appreciated.
Merry Xmas all :-)

[Mark L]

Also be careful of dashes, as in E-Mail. Without brackets, Access reads that to be "E minus Mail"!

A good addition to any professional developers toolbox is Rick Fisher's "Find and Replace". This allows you to make wholesale changes in a database quickly and easily.

[D Willett]

Thanks Mark.