• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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

0
Brogrim
Asked:
Brogrim
1 Solution
 
Dale BurrellDirectorCommented:
select tblMember.MemberID, ...
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
now getting a different error, see image Error Message
0
 
mbizupCommented:
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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]
0
 
mbizupCommented:
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.
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
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
0
 
mbizupCommented:
Try this for your VBA.  I've replaced double quotes with single quotes and seperated out the form reference:

 strSQL = "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

0
 
NorieVBA ExpertCommented:
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.
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
imnorie

When I use your code I am getting

"Too Few Parameters Expected 1"
0
 
mbizupCommented:
Did you try my post at http:#a36961326  ?

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

CurrentDB.Execute strSQL, dbFailOnError
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now