Dynamic Query change field selections

[Sinfathisar]

I am trying to build a function that will create a dynamic query for a chart on a Subreport.

I am not exactly sure I am going about this the right way, but I need the user to be able to change selected fields for use in the query. I have a form with 3 combobox controls for selecting options to change the SQL statement. So far my code only deals with one of these comboboxes for simplicity. There is a button to call my function. Currently, the function is setting hidden text box values based on the combo controls, but I'm not sure if this is redundant.

I am using this as my guide for building the sql, but I am having trouble picking up the values in my text boxes for use in the SQL. http://www.access-programmers.co.uk/forums/showthread.php?t=256441&highlight=dynamic+query

 Option Compare Database
Option Explicit
  
 Public Function CreateQCChartsforReports() As Boolean
  
 'Define variables for Static Chart creation
Dim qdf As dao.QueryDef
Dim SRespTol As String
Dim strSQLStaticSelect As String
Dim strSQLStaticJoin As String
Dim strSQLStaticExpr1 As String
Dim strSQLStaticExpr2 As String
  
 'set variable for static response tolerance
If Forms!Print_Processing_Report!Combo3 = Forms!Print_Processing_Report!Combo3.ItemData(0) Then
        Forms!Print_Processing_Report!SRespTol = "0.1"
    ElseIf Forms!Print_Processing_Report!Combo3 = Forms!Print_Processing_Report!Combo3.ItemData(1) Then
        Forms!Print_Processing_Report!SRespTol = "0.2"
 End If
  
 'separate the sql into partial strings
 
strSQLStaticSelect = "SELECT Static_Repeatability_Test_Table.Static_Repeatability_ID, Static_Repeatability_Test_Table.Static_Test_Item, Static_Repeatability_Test_Table.Collection_Date, Static_Repeatability_Test_Table.Team_ID, Seed_Test_Item_Table.Static_Test_Item_Height, Static_Repeatability_Test_Table.Static_Response_CH1, Static_Repeatability_Test_Table.Static_Response_CH2, Static_Repeatability_Test_Table.Static_Response_CH3, Static_Repeatability_Test_Table.Static_Response_CH4, Seed_Test_Item_Table.Response_Value_CH1, Seed_Test_Item_Table.Response_Value_CH2, Seed_Test_Item_Table.Response_Value_CH3, Seed_Test_Item_Table.Response_Value_CH4,"
 
 strSQLStaticExpr1 = (1 - Forms![Print_Processing_Report]![SRespTol]) * "Seed_Test_Item_Table.[Response_Value_CH2] AS Expr1,"
 
 strSQLStaticExpr2 = (Forms![Print_Processing_Report]![SRespTol] + 1) * "Seed_Test_Item_Table.[Response_Value_CH2] AS Expr2"
 
 strSQLStaticJoin = "FROM Static_Repeatability_Test_Table INNER JOIN Seed_Test_Item_Table ON Static_Repeatability_Test_Table.[Static_Test_Item] = Seed_Test_Item_Table.[Test_Item_ID] " & _
"ORDER BY Static_Repeatability_Test_Table.Static_Test_Item, Static_Repeatability_Test_Table.Collection_Date, Static_Repeatability_Test_Table.Team_ID;"
 
 'Create the query using SQL defined above
Set qdf = CurrentDb.CreateQueryDef("Static_Chart")
qdf.SQL = strSQLStaticSelect & strSQLStaticExpr1 & strSQLStaticExpr2 & strSQLStaticJoin
  
 qdf.Close
 Set qdf = Nothing
 End Function

I haven't gotten as far as calling for the report to open, but I am currently having trouble here:

 strSQLStaticExpr1 = (1 - Forms![Print_Processing_Report]![SRespTol]) * "Seed_Test_Item_Table.[Response_Value_CH2] AS Expr1,"

I get the run-time error 13: type mismatch.

My question is - how do I get a value from an unbound textbox on an unbound form into a string to use as sql? The value in the textbox is a number.

 

[DavidAtWork]

from your error above, I'd say that one of the values is a string value, try using CInt/Clng or CDbl if decimal

strSQLStaticExpr1 = (1 - CInt(Forms![Print_Processing_Report]![SRespTol])) *
CInt(Seed_Test_Item_Table.[Response_Value_CH2])

As for generating a dynamic query, you could try using a temp table which your chart will be based on. Build a SQL string with criteria based on your combo boxes that will append records to the temp table

David

 

[Sinfathisar]

Thanks David,

This ended up getting me the result I was after - close to your suggestion.

 strSQLStaticExpr1 = (1 - CDbl(Forms![Print_Processing_Report]!
  [SRespTol])) & " * [Response_Value_CH2], "

I have a long way to go, but this gets me moving again in the right direction. I'd rather avoid temporary tables for this one so I'm exploring other avenues at the moment.

 

[vbaInet]

Just fyi, remember to check that your unbound controls are returning values before trying to use them. You'll get type mismatch errors with your explicit CInt() cast if it doesn't contain a value.