How do I structure long SQL statements in VBA?
Posted on 2007-11-25
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.