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"