Dynamic query help

[ssmith001]

I have a form where a user chooses different criteria from listboxes and then I run out and format the SQL statement.

This is the query that I am trying to replicate using VBA code. Note: the Location_ID, Ship_Day, and Final_Dest come from the values in the listboxes.

I am getting a syntax error and I cannot find it anywhere. Also, is there an easier way to do this?

SELECT [(Table) Denton Routing].LOCATION_ID
          , [(Table) Location].NAME
          , [(Table) Location].CITY
          , [(Table) Location].STATE
          , [(Table) Location].REGION
          , [(Table) Denton Routing].UNIQUE_LANE_ID
          , [(Table) Denton Routing].CARRIER_ID
          , [(Table) Denton Routing].[SHIP DAY]
          , [(Table) Denton Routing].[DELIVERY DAY]
          , [(Table) Denton Routing].[TIME AT LOCATION]
          , [(Table) Denton Routing].STOP_NUM
          , [(Table) Denton Routing].NO_OFF_STOPS
FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = [(Table) Denton Routing].LOCATION_ID
WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID 
    FROM [(Table) Denton Routing] 
   Where [(Table) Denton Routing].Location_ID = "13176AA")) 
AND (([(Table) Denton Routing].[SHIP DAY])="MONDAY" 
AND [(Table) Denton Routing].Final_Dest = "DENTON"));

Here's the code:

Private Sub cmdRunQuery_Click()

Dim Db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set Db = CurrentDb()

' Delete the existing dynamic query; trap the error if the query does
' not exist.
'On Error Resume Next
'Db.QueryDefs.Delete ("Dynamic_Query")
'On Error GoTo 0

where = Null
where = "WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID FROM [(Table) Denton Routing] where"
where = where & " [(Table) Denton Routing].[Location_ID]= '" + Me![Text35] + "'"
where = where & " AND [(Table) Denton Routing].[Final_Dest]= '" + Me![List29] + "'"
where = where & " AND [(Table) Denton Routing].[Ship Day]= '" + Me![Combo46] + "'))"

MsgBox (where)

Set QD = Db.CreateQueryDef("Dynamic_Query", _
"Select SELECT [(Table) Denton Routing].LOCATION_ID, [(Table) Location].NAME, [(Table) Location].CITY, [(Table) Location].STATE, " & _
" [(Table) Location].REGION, [(Table) Denton Routing].UNIQUE_LANE_ID, [(Table) Denton Routing].CARRIER_ID, [(Table) Denton Routing].[SHIP DAY], " & _
" [(Table) Denton Routing].[DELIVERY DAY], [(Table) Denton Routing].[TIME AT LOCATION], [(Table) Denton Routing].STOP_NUM, " & _
" [(Table) Denton Routing].NO_OFF_STOPS FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = " & _
" [(Table) Denton Routing].LOCATION_ID " & (" where " + Mid(where, 6) & ";"))

DoCmd.OpenQuery "Dynamic_Query"

End Sub

 

[pbaldy]

You've got extraneous parentheses in the WHERE clause (similar to what the QBE adds). I think there's an extra one in there (after ship_day). I'd drop the unnecessary ones, and I think the extra will become obvious.

BTW, the spaces and parentheses in your names are more trouble than they're worth in the long run.

 

[ssmith001]

Thanks. I was able to fight through this issue and it's working now. The problem I have now is how do I display the results of the query in a subform?

I have tried DoCmd.OpenForm ("subfrmDynamicQuery") but it still pops up a new form in spreadsheet view.

 

[Moniker]

DoCmd.OpenForm opens a new form. A SubForm is a control in a main form. The easiest way to add a subform to a main form is to open your main form and then just drag the form you want to be a subform into the main form. Do that from the form list, and the form you want to be a subform can't be open when you drag it on.

 

[pbaldy]

Hard to say the best way not knowing your overall situation. Generally, I would just have a subform that had that query as its source. To be honest, I see nothing "dynamic" about that query that requires it to be built in code. I'd have a saved query that looked at those form controls for their values. Requery the subform when the values are input.

As a style point, I wouldn't have a variable with the same name as a SQL word. It can be confusing, like which is which here:

(" where " + Mid(where, 6)

Further, why add the word "WHERE" to your "where" variable, then turn around and strip it back out on that line?

 

[The_Doc_Man]

If you are thinking "dynamic" query, also look up the topic "Cascading Combo Boxes" - using the forum's SEARCH functions - as a way to vary the focus of a query.