I am using Excel 2000 and MSSQL 2000 and getting inconsistent results
when pulling rows that are between a specific date range.

Here is the statement:

SQL.REQUEST("DSN=TheDB",,1,"SELECT Count(QuoteID) AS CountEvents FROM
[tblQuotes] WHERE DateOrdered BETWEEN " &B3 &" AND " &B4 )

When I run the query from Enterprise Manager in MSSQL2000 I get the
expected results. When I let the result come from the SQL.REQUEST
function in Excel, it omits rows and they are rows in the middle of
the range, not at the boundries. In other words, it's not the usual
problem where I'm not taking into consideration that SQL looks at
2/15/04 as 2/15/04 at 12:00:00AM. Widening the range doesn't seem to
fix the results. B3 and B4 are formatted as date cells and DateOrdered
is a datetime field in the SQL db.

Any help would be greatly appreciated.

I think you need to be a bit more explicit with your date formatting
in the SELECT query e.g. try:

SQL.REQUEST("DSN=TheDB",,1,"SELECT Count(QuoteID) AS CountEvents FROM
[tblQuotes] WHERE DateOrdered BETWEEN '"&TEXT(B3,"dd mmm yyyy")&"' AND
'"&TEXT(B4,"dd mmm yyyy")&"'")

--

(Steve Remer) wrote in message . com...
I am using Excel 2000 and MSSQL 2000 and getting inconsistent results
when pulling rows that are between a specific date range.

Here is the statement:

SQL.REQUEST("DSN=TheDB",,1,"SELECT Count(QuoteID) AS CountEvents FROM
[tblQuotes] WHERE DateOrdered BETWEEN " &B3 &" AND " &B4 )

When I run the query from Enterprise Manager in MSSQL2000 I get the
expected results. When I let the result come from the SQL.REQUEST
function in Excel, it omits rows and they are rows in the middle of
the range, not at the boundries. In other words, it's not the usual
problem where I'm not taking into consideration that SQL looks at
2/15/04 as 2/15/04 at 12:00:00AM. Widening the range doesn't seem to
fix the results. B3 and B4 are formatted as date cells and DateOrdered
is a datetime field in the SQL db.

Any help would be greatly appreciated.

That did it!! Thanks so much!


(onedaywhen) wrote in message . com...
I think you need to be a bit more explicit with your date formatting
in the SELECT query e.g. try:

SQL.REQUEST("DSN=TheDB",,1,"SELECT Count(QuoteID) AS CountEvents FROM
[tblQuotes] WHERE DateOrdered BETWEEN '"&TEXT(B3,"dd mmm yyyy")&"' AND
'"&TEXT(B4,"dd mmm yyyy")&"'")