I can't seem to get this create table query to work within a form. Meanwhile, if I take the exact same code and place it into a query all by itself it works!!
Here is the code from the query that works (table names and variables were changed to be more generic):
SELECT TableA.VariableA, MonthName(Month(TableB.Date)) & ' ' & Day(TableB.Date) AS ProblemDate INTO TableC
FROM TableA, TableB
WHERE (((TableA.VariableA) = [TableB].[VariableA])) And [TableB].[Problem] = 2
AND [TableB].[Date] BETWEEN #01/01/2002# AND #01/01/2003#;
Here's the same code from inside my form.
Dim ProblemSQL, ProblemTbl as String
ProblemTbl = "TableC"
ProblemSQL = "SELECT TableA.VariableA, MonthName(Month(TableB.Date)) & ' ' & Day(TableB.Date) AS ProblemDate INTO " & ProblemTbl & " "
ProblemSQL = ProblemSQL & "FROM TableA, TableB "
ProblemSQL = ProblemSQL & "WHERE (((TableA.VariableA) = [TableB].[VariableA])) "
ProblemSQL = ProblemSQL & "AND [TableB].[Problem] = " & combo_box_problem.Column(0) & " "
ProblemSQL = ProblemSQL & "AND [TableB].[Date] BETWEEN #01/01/" & combo_year.Column(0) & "# AND #01/01/" & combo_year.Column(0) & "#;"
Msgbox ProblemSQL
DoCmd.RunSQL ProblemSQL
DoCmd.OpenTable ProblemTbl, acViewNormal
Why is it working with one and not the other?
Here is the code from the query that works (table names and variables were changed to be more generic):
SELECT TableA.VariableA, MonthName(Month(TableB.Date)) & ' ' & Day(TableB.Date) AS ProblemDate INTO TableC
FROM TableA, TableB
WHERE (((TableA.VariableA) = [TableB].[VariableA])) And [TableB].[Problem] = 2
AND [TableB].[Date] BETWEEN #01/01/2002# AND #01/01/2003#;
Here's the same code from inside my form.
Dim ProblemSQL, ProblemTbl as String
ProblemTbl = "TableC"
ProblemSQL = "SELECT TableA.VariableA, MonthName(Month(TableB.Date)) & ' ' & Day(TableB.Date) AS ProblemDate INTO " & ProblemTbl & " "
ProblemSQL = ProblemSQL & "FROM TableA, TableB "
ProblemSQL = ProblemSQL & "WHERE (((TableA.VariableA) = [TableB].[VariableA])) "
ProblemSQL = ProblemSQL & "AND [TableB].[Problem] = " & combo_box_problem.Column(0) & " "
ProblemSQL = ProblemSQL & "AND [TableB].[Date] BETWEEN #01/01/" & combo_year.Column(0) & "# AND #01/01/" & combo_year.Column(0) & "#;"
Msgbox ProblemSQL
DoCmd.RunSQL ProblemSQL
DoCmd.OpenTable ProblemTbl, acViewNormal
Why is it working with one and not the other?