?
Solved

DoCmd.RunSQL Problem

Posted on 2011-10-10
9
Medium Priority
?
272 Views
Last Modified: 2012-08-14
I am trying to run an SQL statement in my VBA, the statement is very long and will not let me execute.
INSERT INTO tempMember_Sport ( MemberID, Name, Telephone, Age, IWASportID, IWASport, Address, lblName, frmAddress, lblAddress, Address2, Address3, lblCounty, MemberSportType, MemberSportTypeID )
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, tblSportMemberType.MemberSportType, tblSportMember.MemberSportTypeID
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
WHERE (((tblIWASport.IWASportD)=[Forms]![StartUp]![lstSportID]))
ORDER BY Trim(Trim(nz([SecondName],"")) & " " & nz([FirstName],""));

Open in new window

0
Comment
Question by:Brogrim
9 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 36943570
How exactly are you using that in VBA?

Are you assigning it to a string variable then using that variable with DoCmd.RunSQL?

0
 

Author Comment

by:Brogrim
ID: 36943821
I have built the query and am now starting the 1st line of code with

docmd.runsql
0
 
LVL 35

Expert Comment

by:Norie
ID: 36943923
How have you built the query?

Did you use a string variable and then pass that to DoCmd.RunSQL?

What is the problem?

Does the query not work?

Do you get syntax errors when building it?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Brogrim
ID: 36944008
The query works, I have a command button where i start my code

docmd.runsql

The problem is that the statement is too long, it shows up red on different lines, I cannot put it all on the one line
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36944028
It would help to know what the errors are, but I noticed what I would consider to be a misspelling - tblIWASport.IWASportD appears 3 times in the statement, should this be tblIWASport.IWASportID?  (Missing the I in ID)
0
 

Author Comment

by:Brogrim
ID: 36944059
The query works

as outlined above when I copy the statement from the query builder it goes across 5 lines with the 4 at the bottom in red.

VB will not allow me to use backspace to put the code on one line
0
 
LVL 35

Accepted Solution

by:
Norie earned 1000 total points
ID: 36944429
The don't think the query is too long.

The reason you get the red text is because there are syntax errors in the code.

That could be for a no of reasons, one of which is the use of double quotes throughout the query.

You can't just copy from the SQL view straight into code.

I take it you saved the query when you created it in the QBE.

If you have you can try this.
Sub RunSavedQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQryName As String
Dim strSQL As String

    Set db = CurrentDb
   
    ' replace  'YourQuery' with the name of the query you want to execute.
    strQryName = "YourQuery"
       
    Set qdf = db.QueryDefs(strQryName)
    
    strSQL = qdf.SQL
    
    DoCmd.SetWarnings  False ' optional, turns off 'You are about to append...' message

    DoCmd.RunSQL strSQL

    DoCmd.SetWarnings  True ' turns warnings on again

End Sub

Open in new window

0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1000 total points
ID: 36944547
You need to replace ""s with '', " " with ' ', and place any Form references outside of your SQL string:

Dim strSQL As String
strSQL = "INSERT INTO tempMember_Sport ( MemberID, Name, Telephone, Age, IWASportID, IWASport, Address, lblName, frmAddress, lblAddress, " _
    & "Address2, Address3, lblCounty, MemberSportType, MemberSportTypeID )" _
    & "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, " _
    & "tblSportMemberType.MemberSportType, tblSportMember.MemberSportTypeID " _
    & "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 " _
    & "WHERE (((tblIWASport.IWASportD) = " & [Forms]![StartUp]![lstSportID] & ")) " _
    & "ORDER BY Trim(Trim(nz([SecondName],'')) & ' ' & nz([FirstName],''));"
CurrentDB.execute strSQL,dbfailonerror

Open in new window

0
 
LVL 35

Expert Comment

by:Norie
ID: 36944656
mbizup

I think, but I'm not sure, that [Forms]![StartUp]![lstSportID] is a parameter in the query.

As far as I can work out the SQL is from the query builder.

See #36944059.

copy the statement from the query builder

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question