?
Solved

How do I structure long SQL statements in VBA?

Posted on 2007-11-25
4
Medium Priority
?
945 Views
Last Modified: 2013-11-27
Hi experts,

I am trying to structure my SQL statement in VBA better than having it all in one line.  My main reason for wanting to do this, apart from it being easier to read, is that I have some SQL statements that appear to be too long for one line in VBA.

The code I am using, although not my longest is as follows:

Dim SQL as String

SQL = "SELECT tblPlan.ID, tblPlan.Criticality, tblPlan.Plan_Prod_Serv_Name, tblPlan.LRef, tblPlan.LKey, tblPlan.Business_Unit_ID" & _
        "FROM tblPlan" & _
        "WHERE (((tblPlan.LRef) = [Forms]![switchboard]![LRef]) And ((tblPlan.LKey) = [Forms]![switchboard]![LKey]) And ((tblPlan.Business_Unit_ID) = IIf([Forms]![switchboard]![SwitchGroup].[Form]![UnitID] = 1, [Forms]![switchboard]![SwitchGroup].[Form]![UnitID], [Forms]![switchboard]![SwitchGroup].[Form]![UnitID])))" & _
        "ORDER BY tblPlan.Criticality DESC , tblPlan.Plan_Prod_Serv_Name;"

The above SQL needs to go into the rowsource of my listbox on the form.  The listbox is called lstPlans and the code to get the SQL into lstPlans is as follows:

Me.lstPlans.RowSource = SQL

This is not working and I do not know why.  I have no errors coming up and I am separating each line of code with "& _".  Please help as I must be doing something wrong?

I create my SQL statements using the Query Builder, so if anybody has a better solution as opposed to using an SQL statement in my VBA code I would be grateful to read them.

Thank you.

Terry
0
Comment
Question by:TerenceHewett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1600 total points
ID: 20346555
Hello TerenceHewett,

SQL = "SELECT ID, Criticality, Plan_Prod_Serv_Name, LRef, LKey" & _
    "Business_Unit_ID FROM tblPlan " & _
    "WHERE LRef = '" & [Forms]![switchboard]![LRef]) & "' And LKey = '" & _
    [Forms]![switchboard]![LKey] & "' And Business_Unit_ID = IIf(" & _
    ([Forms]![switchboard]![SwitchGroup].[Form]![UnitID] = 1) & ", '" & _
    [Forms]![switchboard]![SwitchGroup].[Form]![UnitID] "', '" & _
    [Forms]![switchboard]![SwitchGroup].[Form]![UnitID]"') " & _
    "ORDER BY Criticality DESC , Plan_Prod_Serv_Name;"


Regards,

Patrick
0
 

Author Comment

by:TerenceHewett
ID: 20346613
Thanks Patrick for your help.  What is the main difference from what I had and why will your statement work? I would like to try and understand what I am doing wrong as opposed to just using your code which I am sure will work.

Thank you again.
Terry
0
 
LVL 10

Assisted Solution

by:LennyGray
LennyGray earned 400 total points
ID: 20346675
Patrick's code is correct but I prefer breaking the code down into readable portions and simply concatenate the sqlstatement like below:

   
 sqlString = "SELECT tblDT_ComponentYear.iComponentYearID, tblDT_ComponentYear.sDocumentName "
    sqlString = sqlString & "FROM tblDT_ComponentYear "
    sqlString = sqlString & "WHERE (((tblDT_ComponentYear.sDocumentName) Is Not Null and (tblDT_ComponentYear.sDocumentName)<>"""") AND ((tblDT_ComponentYear.iAwardYear)= " & iAwardYear & "))"
 
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset(sqlString, dbOpenDynaset)

Open in new window

0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1600 total points
ID: 20346713
Terry,

Excellent questions, and sorry I did not spend more time on the explanation, which in any event is
more valuable than the answer :)

As I see it, the main differences between my code and yours are:

1) You did not allow for spaces between the clauses in your SQL statement, such as between the
last field in the SELECT clause and the beginning of the FROM clause.  This will make your code
fail.

2) I treated the "query by form" values differently by embedding the actual values into the SQL
string.  The way you did it would be just fine in a regular saved query, but you cannot use that
syntax when you build the SQL string dynamically in VBA.

I also stripped out the table name qualifier on the field instances, but that's small beer.

Regards,

Patrick
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

801 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