• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 722
  • Last Modified:

Catastrophic Failure

I have a problem on only one computer with this query :

SELECT Count(Call_ID) AS CompteDeCall_ID FROM dbo_Call WHERE Call_DateEntered > #2005-03-21#

It's a really simple query that count the number of item since a date.
on most computer it work, but one give this error Catastrophic Failure
i looked on the MS KB but nothing that worked...

I modified the query to test and this one
SELECT Count(Call_ID) AS CompteDeCall_ID FROM dbo_Call WHERE Call_DateEntered > Now()
work fine.
the problem is the date maybe the # or the format or the computer...?

for information i'm using ADO 2.8 (both 2.5 or 2.1 given the same error)
the dbo_Call is a linked table on SQL Server 2000 using Access ODBC
I tried using DAO 3.6 and it crash.

the offending computer has Windows XP SP2
1 Solution
Steve BinkCommented:
It could be the absolute date reference is confusing Access.  Access is SUPPOSED to be able to identify different date formats with no problem, but the reality is somewhat different.  Try using the DateValue() method to provide the comparison value:

SELECT Count(Call_ID) AS CompteDeCall_ID FROM dbo_Call WHERE Call_DateEntered > DateValue("3/21/2005")

Also, here's some documentation from the help file.  Make sure you check your date format settings:

DateValue recognizes the order for month, day, and year according to the Short Date format you specified for your system. DateValue also recognizes unambiguous dates that contain month names, either in long or abbreviated form. For example, in addition to recognizing 12/30/1991 and 12/30/91, DateValue also recognizes December 30, 1991 and Dec 30, 1991.
These links may be of use to you:

It looks like you need a hotfix from MS.
Is there any chance of a regional settings issue here?
numerissAuthor Commented:
the DateValue worked, but stripped the Time of some of my dates.
so I simply used the CDate fonction and it worked...

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now