asked on

synthax error in INSERT INTO statement

Getting the error when writing this statement

Dim strSQL As String

strSQL = "INSERT INTO tempMember_Sport ( MemberID, Name, Telephone, Age, SportAgeGroupID, IWASportID, IWASport, Address, lblName, frmAddress, lblAddress, " _
    & "Address2, Address3, lblCounty, SportAffiliationTypeID, SportAffiliationType )" _
    & "tblMember.MemberID, Trim(Trim(nz([SecondName],"")) & ' ' & nz([FirstName],"")) AS Name, tblMember.Telephone, Year(Now())-Year([DOB]) AS Age, " _
    & "tblSportMember.SportAgeGroupID, tblIWASport.IWASportD, tblIWASport.IWASport, " _
    & "Trim(Trim(Trim(Trim(Trim(Trim(Trim(Trim(nz([HouseNo],'') & nz([HouseLetter],'')) & ' ' & nz([HouseName],'')) & ' ' & nz([Address1],''))" _
    & ") & ' ' & nz([Address2],'')) & ' ' & nz([Address3],'')) & ' ' & nz([County],'')) & ' ' & nz([PostCode],'')) & ' ' & nz([PostCodePrefix],'')) AS Address," _
    & ") FirstName] & ' ' & [SecondName] AS lblName, Trim(Trim(Trim(Trim(Trim(Trim(Trim(Trim(nz([HouseNo],'') & nz([HouseLetter],'')) & ' ' & nz([HouseName],''))" _
    & ") ' ' & nz([Address1],'')) & ' ' & nz([Address2],'')) & ' ' & nz([Address3],'')) & ' ' & nz([County],'')) & ' ' & nz([PostCode],'')) & ' ' & nz([PostCodePrefix],''))" _
    & ") AS frmAddress, Trim(Trim(Trim(nz([HouseNo],'') & nz([HouseLetter],'')) & ' ' & nz([HouseName],'')) & ' ' & nz([Address1],'')) &" _
    & ") AS lblAddress, tblMember.Address2, tblMember.Address3, Trim(Trim(nz([County],'')) & ' ' & nz([PostCode],'')) & ' ' & nz([PostCodePrefix],'') AS lblCounty, " _
    & "tblSportMember_AffiliationTypeID.SportAffiliationTypeID , tblSportAffiliationType.SportAffiliationType " _
    & "FROM ((tblIWASport INNER JOIN (tblSportMemberType INNER JOIN ((lktblCounty INNER JOIN (tblSportMember INNER JOIN tblMember ON tblSportMember.MemberID = tblMember.MemberID) " _
    & "ON lktblCounty.CountyID = tblMember.CountyID) INNER JOIN tblMember_IWASport ON tblSportMember.MemberID = tblMember_IWASport.MemberID) ON tblSportMemberType.MemberSportTypeID = " _
    & "tblSportMember.MemberSportTypeID) ON tblIWASport.IWASportD = tblMember_IWASport.IWASportID) INNER JOIN tblSportMember_AffiliationTypeID ON tblSportMember.MemberID = tblSportMember_AffiliationTypeID.MemberID) INNER JOIN tblSportAffiliationType ON tblSportMember_AffiliationTypeID.SportAffiliationTypeID = tblSportAffiliationType.SportAffiliationTypeID " _
    & "WHERE (((tblIWASport.IWASportD) = " & [Forms]![StartUp]![lstSportID] & ")) " _
    & "ORDER BY Trim(Trim(nz([SecondName],'')) & ' ' & nz([FirstName],''));"

Open in new window

Dale Burrell

select tblMember.MemberID, ...

Brogrim

ASKER

now getting a different error, see image

mbizup

Add the following line immediately after your "strSQL = " lines, and before you run or execute the query:
Debug.Print strSQL

Copy the text that gets printed in the Immediate Pane of the VBA Editor, and post it in your next comment, masking any confidential information.

Scott McDaniel (EE MVE )

Note too that "Name" is a Reserved Word, and should not be used as a Column Name. Instead use something like CustomerName, or enclose it in square brackets: AS [Name]

mbizup

With a query this ugly, my own approach would be to use a recordset rather than a single SQL statement like this (more readable/easier to maintain).

Also a debugging tip - get your SELECT statement working first, and then work on turning it into an INSERT query.

Brogrim

ASKER

I have attached the error message

CurrentDb.Execute strSQL, dbFailOnError was at the bottom of the statement

I have built the query in query design and it is running.

I have posted the query code again

Thanks for the help

INSERT INTO tempMember_Sport ( MemberID, SName, Telephone, Age, IWASportID, IWASport, Address, lblName, frmAddress, lblAddress, Address2, Address3, lblCounty, SportAffiliationTypeID, SportAffiliationType )
SELECT tblMember.MemberID, Trim(Trim(nz([SecondName],"")) & " " & nz([FirstName],"")) AS Name, tblMember.Telephone, Year(Now())-Year([DOB]) AS Age, tblIWASport.IWASportD, tblIWASport.IWASport, Trim(Trim(Trim(Trim(Trim(Trim(Trim(Trim(nz([HouseNo],"") & nz([HouseLetter],"")) & " " & nz([HouseName],"")) & " " & nz([Address1],"")) & " " & nz([Address2],"")) & " " & nz([Address3],"")) & " " & nz([County],"")) & " " & nz([PostCode],"")) & " " & nz([PostCodePrefix],"")) AS Address, [FirstName] & " " & [SecondName] AS lblName, Trim(Trim(Trim(Trim(Trim(Trim(Trim(Trim(nz([HouseNo],"") & nz([HouseLetter],"")) & " " & nz([HouseName],"")) & " " & nz([Address1],"")) & " " & nz([Address2],"")) & " " & nz([Address3],"")) & " " & nz([County],"")) & " " & nz([PostCode],"")) & " " & nz([PostCodePrefix],"")) AS frmAddress, Trim(Trim(Trim(nz([HouseNo],"") & nz([HouseLetter],"")) & " " & nz([HouseName],"")) & " " & nz([Address1],"")) AS lblAddress, tblMember.Address2, tblMember.Address3, Trim(Trim(nz([County],"")) & " " & nz([PostCode],"")) & " " & nz([PostCodePrefix],"") AS lblCounty, tblSportMember_AffiliationTypeID.SportAffiliationTypeID, tblSportAffiliationType.SportAffiliationType
FROM tblIWASport INNER JOIN (((tblSportMemberType INNER JOIN ((lktblCounty INNER JOIN (tblSportMember INNER JOIN tblMember ON tblSportMember.MemberID = tblMember.MemberID) ON lktblCounty.CountyID = tblMember.CountyID) INNER JOIN tblMember_IWASport ON tblSportMember.MemberID = tblMember_IWASport.MemberID) ON tblSportMemberType.MemberSportTypeID = tblSportMember.MemberSportTypeID) INNER JOIN tblSportMember_AffiliationTypeID ON tblSportMember.MemberID = tblSportMember_AffiliationTypeID.MemberID) INNER JOIN tblSportAffiliationType ON tblSportMember_AffiliationTypeID.SportAffiliationTypeID = tblSportAffiliationType.SportAffiliationTypeID) ON tblIWASport.IWASportD = tblMember_IWASport.IWASportID
WHERE (((tblIWASport.IWASportD)=[Forms]![StartUp]![lstSportID]))
ORDER BY Trim(Trim(nz([SecondName],"")) & " " & nz([FirstName],""));

Open in new window

error-message.JPG

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

If you've built the query in design view try this.

strSQL = CurrentDB.QueryDefs("QueryName").SQL

CurrentDB.Execute strSQL

Open in new window

Replace 'QueryName' with the name of the saved query.

Brogrim

ASKER

imnorie

When I use your code I am getting

"Too Few Parameters Expected 1"

mbizup

Did you try my post at http:#a36961326 ?

You will need the following immediately after the "strSQL = ... " lines:

CurrentDB.Execute strSQL, dbFailOnError