Using BETWEEN with Dates

[JC10001]

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?

 

[KenHigg]

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] = " & me!combo_box_problem.Column(0) & " "
ProblemSQL = ProblemSQL & "AND [TableB].[Date] BETWEEN #01/01/" & me!combo_year.Column(0) & "# AND #01/01/" & me!combo_year.Column(0) & "#;"

???
kh

 

[JC10001]

No luck Ken, but I do appreciate your help.

I tried putting my original SQL string all on the same line and it worked!! I have no idea why splitting it up was causing a problem unless I wasn't splitting it up properly somehow.

Again, Thanks.

 

[KenHigg]

I would put a breakpoint in and view the sql in the immediate window. Sometimes that's the only way I can figure out what the actual statement looks like to the interpreter...

kh

 

[KenHigg]

Never mind - I see you used the message box to view the sql statement...

kh

 

[Jon K]

Not sure if this was the problem, since you haven't said what it was. In the query, you have
..... AND [TableB].[Date] BETWEEN #01/01/2002# AND #01/01/2003#;

In ProblemSQL it is
..... "AND [TableB].[Date] BETWEEN #01/01/" & combo_year.Column(0) & "# AND #01/01/" & combo_year.Column(0) & "#;"


So if combo_year.Column(0) is 2002, you will have
..... AND [TableB].[Date] BETWEEN #01/01/2002# AND #01/01/2002#;

which is only one day, not a year and a day as in the query.
.