Query on Date range does not qualify the Year

I have a query that pulls data based on a date range. The data that comes
out is correct based on the Month and Day values, however the year seems to
not factor into the query. For instance, I am looking at dates from years
2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive
results from 06/01 thru 07/01 from both 2004 and 2005.

Here is what my query looks like:

BeginDate = Format(Date, "mm/dd/yyyy")
EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy")

SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate >= '" & _
BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate"

Looks like what you are getting is an alpha sort based on the text string,
not a date sort based on date values. The way to fix it depends on your data
source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the
dates in their native numeric format.

"Malcolm Makin" wrote:

> I have a query that pulls data based on a date range. The data that comes
> out is correct based on the Month and Day values, however the year seems to
> not factor into the query. For instance, I am looking at dates from years
> 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive
> results from 06/01 thru 07/01 from both 2004 and 2005.
>
> Here is what my query looks like:
>
> BeginDate = Format(Date, "mm/dd/yyyy")
> EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy")
>
> SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate >= '" & _
> BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate"
>
>

My data source is another Excel workbook. The dates were entered by default
as m/d/yyyy. I have then set the explicit format on the cells to be
mm/dd/yyyy. However, the query pulls data regardless of year. Like I
metioned before, the Month/Day range is correct, just not the year. I have
also tried the #mm/dd/yyyy method in my query, but it does not help.

"K Dales" wrote:

> Looks like what you are getting is an alpha sort based on the text string,
> not a date sort based on date values. The way to fix it depends on your data
> source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the
> dates in their native numeric format.
>
> "Malcolm Makin" wrote:
>
> > I have a query that pulls data based on a date range. The data that comes
> > out is correct based on the Month and Day values, however the year seems to
> > not factor into the query. For instance, I am looking at dates from years
> > 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive
> > results from 06/01 thru 07/01 from both 2004 and 2005.
> >
> > Here is what my query looks like:
> >
> > BeginDate = Format(Date, "mm/dd/yyyy")
> > EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy")
> >
> > SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate >= '" & _
> > BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate"
> >
> >

That is consistent with an alphabetic sort - in alphabetic terms both
"06/01/2004" and "06/02/2005" are >= "06/01/2005" and <= "07/01/2005" (alpha
sort looks at each character left to right).

If you are using Excel data - and if the parameters were entered AS DATE
VALUES (regardless of cell formatting), don't change the dates at all - just
use WHERE (reqdate >= " & BeginDate & ") AND (reqdate <= " & EndDate & ")"

If, though, the dates are actually stored as TEXT values, then convert them
first:
CBeginDate = DateValue(BeginDate)
CEndDate = DateValue(EndDate)
And then I think (not sure, never had to do it this way) you could use
WHERE (DateValue(reqdate) >= " & CBeginDate & ") AND (DateValue(reqdate) <=
" & CEndDate & ")"


"Malcolm Makin" wrote:

> My data source is another Excel workbook. The dates were entered by default
> as m/d/yyyy. I have then set the explicit format on the cells to be
> mm/dd/yyyy. However, the query pulls data regardless of year. Like I
> metioned before, the Month/Day range is correct, just not the year. I have
> also tried the #mm/dd/yyyy method in my query, but it does not help.
>
> "K Dales" wrote:
>
> > Looks like what you are getting is an alpha sort based on the text string,
> > not a date sort based on date values. The way to fix it depends on your data
> > source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the
> > dates in their native numeric format.
> >
> > "Malcolm Makin" wrote:
> >
> > > I have a query that pulls data based on a date range. The data that comes
> > > out is correct based on the Month and Day values, however the year seems to
> > > not factor into the query. For instance, I am looking at dates from years
> > > 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive
> > > results from 06/01 thru 07/01 from both 2004 and 2005.
> > >
> > > Here is what my query looks like:
> > >
> > > BeginDate = Format(Date, "mm/dd/yyyy")
> > > EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy")
> > >
> > > SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate >= '" & _
> > > BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate"
> > >
> > >

Now that you brought up the point of an alphabetic sort, I've realized what
is happening here. The results has defied all logic because it obviously has
not been treating the values as dates (otherwise the query would have
worked). I examined the column in the Excel sheet, and realized that there
are 2 or 3 extra rows with comments or other text in this column, before any
Date actually occurs.

So what is happening is that Excel during the query determines the data type
for this column to be text rather than a date. I have tried using DateValue
in the query like you suggested, but alas it did not accept it (was worth a
try). I'm not sure I can convince those whom I am creating this query for to
simply delete the extra text, so do you have any further suggestions on how
to "force" the query to recognize the column as a date?

I suppose as a work around, I could check each value for the year with an IF
statement, after the query is actually run (I'm using ADO rather than MS
Query, for more flexibility).

"K Dales" wrote:

> That is consistent with an alphabetic sort - in alphabetic terms both
> "06/01/2004" and "06/02/2005" are >= "06/01/2005" and <= "07/01/2005" (alpha
> sort looks at each character left to right).
>
> If you are using Excel data - and if the parameters were entered AS DATE
> VALUES (regardless of cell formatting), don't change the dates at all - just
> use WHERE (reqdate >= " & BeginDate & ") AND (reqdate <= " & EndDate & ")"
>
> If, though, the dates are actually stored as TEXT values, then convert them
> first:
> CBeginDate = DateValue(BeginDate)
> CEndDate = DateValue(EndDate)
> And then I think (not sure, never had to do it this way) you could use
> WHERE (DateValue(reqdate) >= " & CBeginDate & ") AND (DateValue(reqdate) <=
> " & CEndDate & ")"
>
>
> "Malcolm Makin" wrote:
>
> > My data source is another Excel workbook. The dates were entered by default
> > as m/d/yyyy. I have then set the explicit format on the cells to be
> > mm/dd/yyyy. However, the query pulls data regardless of year. Like I
> > metioned before, the Month/Day range is correct, just not the year. I have
> > also tried the #mm/dd/yyyy method in my query, but it does not help.
> >
> > "K Dales" wrote:
> >
> > > Looks like what you are getting is an alpha sort based on the text string,
> > > not a date sort based on date values. The way to fix it depends on your data
> > > source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the
> > > dates in their native numeric format.
> > >
> > > "Malcolm Makin" wrote:
> > >
> > > > I have a query that pulls data based on a date range. The data that comes
> > > > out is correct based on the Month and Day values, however the year seems to
> > > > not factor into the query. For instance, I am looking at dates from years
> > > > 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive
> > > > results from 06/01 thru 07/01 from both 2004 and 2005.
> > > >
> > > > Here is what my query looks like:
> > > >
> > > > BeginDate = Format(Date, "mm/dd/yyyy")
> > > > EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy")
> > > >
> > > > SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate >= '" & _
> > > > BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate"
> > > >
> > > >

I can see why that is a problem now. Those comments are going to be a
problem since it means that the column MUST be a text field for ADO purposes.
One way to get around this, if the structure of the spreadsheet permits it,
would be to create a named range that will exclude the rows with the comments.

You are right that you could do a workaround, but a simple IF is not going
to help in sorting (unless you want to write your own sort routine!). The
fact that you are using ADO instead of MSQuery will help, since you can
minipulate the recordset in code. This depends on what you do with the
recordset once you have retrieved it. But here is one solution I have in
mind: Disconnect the recordset (i.e. close the connection while saving the
recordset in memory - if you are not sure how to do this look in an ADO
reference or online help for "ADO disconnected recordset"). Then you could
add a field (Recordset.Fields.Add) and specify that it will hold a date value
(Field.Type = adDate). Then loop through the recordset - check the value of
the "problem" date field with IsDate(). If so, set the new field value (the
field you created) to the true date using DateValue() to convert it. Then
you should be able to sort (Recordset.Sort) accurately by the NEW date field.
This is a complex workaround, though, and there might be an easier way -
again depending how you are ultimately going to use the recordset.

Sometimes these seemingly "simple" problems turn out to be quite complex!
If only your users understood the impact of mixing data types in a column!!!

"Malcolm Makin" wrote:

> Now that you brought up the point of an alphabetic sort, I've realized what
> is happening here. The results has defied all logic because it obviously has
> not been treating the values as dates (otherwise the query would have
> worked). I examined the column in the Excel sheet, and realized that there
> are 2 or 3 extra rows with comments or other text in this column, before any
> Date actually occurs.
>
> So what is happening is that Excel during the query determines the data type
> for this column to be text rather than a date. I have tried using DateValue
> in the query like you suggested, but alas it did not accept it (was worth a
> try). I'm not sure I can convince those whom I am creating this query for to
> simply delete the extra text, so do you have any further suggestions on how
> to "force" the query to recognize the column as a date?
>
> I suppose as a work around, I could check each value for the year with an IF
> statement, after the query is actually run (I'm using ADO rather than MS
> Query, for more flexibility).
>
> "K Dales" wrote:
>
> > That is consistent with an alphabetic sort - in alphabetic terms both
> > "06/01/2004" and "06/02/2005" are >= "06/01/2005" and <= "07/01/2005" (alpha
> > sort looks at each character left to right).
> >
> > If you are using Excel data - and if the parameters were entered AS DATE
> > VALUES (regardless of cell formatting), don't change the dates at all - just
> > use WHERE (reqdate >= " & BeginDate & ") AND (reqdate <= " & EndDate & ")"
> >
> > If, though, the dates are actually stored as TEXT values, then convert them
> > first:
> > CBeginDate = DateValue(BeginDate)
> > CEndDate = DateValue(EndDate)
> > And then I think (not sure, never had to do it this way) you could use
> > WHERE (DateValue(reqdate) >= " & CBeginDate & ") AND (DateValue(reqdate) <=
> > " & CEndDate & ")"
> >
> >
> > "Malcolm Makin" wrote:
> >
> > > My data source is another Excel workbook. The dates were entered by default
> > > as m/d/yyyy. I have then set the explicit format on the cells to be
> > > mm/dd/yyyy. However, the query pulls data regardless of year. Like I
> > > metioned before, the Month/Day range is correct, just not the year. I have
> > > also tried the #mm/dd/yyyy method in my query, but it does not help.
> > >
> > > "K Dales" wrote:
> > >
> > > > Looks like what you are getting is an alpha sort based on the text string,
> > > > not a date sort based on date values. The way to fix it depends on your data
> > > > source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the
> > > > dates in their native numeric format.
> > > >
> > > > "Malcolm Makin" wrote:
> > > >
> > > > > I have a query that pulls data based on a date range. The data that comes
> > > > > out is correct based on the Month and Day values, however the year seems to
> > > > > not factor into the query. For instance, I am looking at dates from years
> > > > > 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive
> > > > > results from 06/01 thru 07/01 from both 2004 and 2005.
> > > > >
> > > > > Here is what my query looks like:
> > > > >
> > > > > BeginDate = Format(Date, "mm/dd/yyyy")
> > > > > EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy")
> > > > >
> > > > > SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate >= '" & _
> > > > > BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate"
> > > > >
> > > > >