Using BETWEEN with Dates (1 Viewer)

[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.
.

 

[Pat Hartman]

Sugestions:

1. Don't convert the date to month and year when you extract it. Simply export the date. You can always format it as year and month any time you want to use it.
2. NEVER use reserved words, especially function names, as column names. Change your [Date] to some other name before it causes a problem.
3. Between includes values at BOTH ends so you are selecting 1 year plus 1 day. If you only want 1 calendar year, you can simplify the criteria to -
   ".... Year([TableB].[Date]) = " & Me.combo_year