Error Access query on SQL linked table

[cwy]

I'm having trouble getting a query to work while linked to MS SQL Server 7.0 tables from MS Access 2000. I get an "ODBC -- Call Failed" after I'm prompted for the Parameters. Could it be that the evaluation of the 'between' statements in the WHERE clause are not be evaluated properly or at all? The query is as follows:

============ Start Query ============
PARAMETERS [Beginning Date or leave blank for all] DateTime, [Ending Date or leave blank for all] DateTime, [Enter Staff Number or leave blank for All] Long;
SELECT DISTINCTROW CALENDAR.CASETICKLE, CALENDAR.CalDATE, CALENDAR.CTIME, CALENDAR.SNUM, CALENDAR.CASENUM, CALENDAR.RTICKLE, CALENDAR.REASON, CLIENTSW.CLNAME, CLIENTSW.CFNAME, CLIENTSW.CPHONE, subRTICKLE.RTICREA, CLIENTSW.DOPEN, CLIENTSW.CASETYPE, SMEMBER.SINITIALS
FROM subRTICKLE RIGHT JOIN (SMEMBER RIGHT JOIN (CLIENTSW RIGHT JOIN CALENDAR ON CLIENTSW.CASENUM = CALENDAR.CASENUM) ON SMEMBER.SNUM = CALENDAR.SNUM) ON subRTICKLE.RTickle = CALENDAR.RTICKLE
WHERE (((CALENDAR.CalDATE) Between [Beginning Date or leave blank for all] And [Ending Date or leave blank for all]) AND ((CALENDAR.SNUM)=[Enter Staff Number or leave blank for All])) OR (((CALENDAR.CalDATE) Between [Beginning Date or leave blank for all] And [Ending Date or leave blank for all]) AND (([Enter Staff Number or leave blank for all]) Is Null)) OR (((CALENDAR.SNUM)=[Enter Staff Number or leave blank for All]) AND (([CALENDAR].[CalDATE] Between [Beginning Date or leave blank for all] And [Ending Date or leave blank for all]) Is Null)) OR ((([Enter Staff Number or leave blank for all]) Is Null) AND (([CALENDAR].[CalDATE] Between [Beginning Date or leave blank for all] And [Ending Date or leave blank for all]) Is Null))
ORDER BY CALENDAR.CalDATE, CALENDAR.CTIME, CALENDAR.SNUM;

============ End Query ============

 

[tlbroadbent]

ODBC Call Failed is a fairly generic message. Is the any additional information provided in the error message? Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[sgursahaney]

Cwy:
We've done a lot of MS Access queries to a SQL Server 7.0 DB backend. We used to get this innocuous error all the time. Typically, it means that your SQL statement is not formatted correctly or is taking too long to execute. To solve the problem, you need to isolate the source of the problem. First, try running the query without the parameters. Hardcode all of the parameters in the query with the values you would have entered and attempt to run the query again. If it works, then the problem has to do with how the parameters are being specified or defined. If it doesn't work, then your SQL statement is at fault. My philosophy is to start with a query that is simple and works and then add the complexity (parameters, joins, etc.) in small pieces until you get want you finally want.

Hope that helps!

Regards,
Suresh