Text Parameter causes an ODBC--Call failed error

[Guest]

This is a query I've used regularly in Access 2.0 and Access97 with no
problems.

Interesting enough, when I remove the "Document No" parameter, I don't get
an ODBC error. Instead, I am prompted for the "date" and after I type in
"01/31/03" the records display. Any suggestions...comments?

See SQL code below. Thanks,

Vince

SELECT DISTINCTROW ARLINH.Docno, ARLINH.Location, ARLINH.Custkey,
ARLINH.Itemkey, ARLINH.Unitprice, ARLINH.Ordqty, [Unitprice]*[Ordqty] AS
[Sale Amount], ARLINH.Docdate, ARLINH.Cost, [Sale Amount]-[Cost]*[Ordqty] AS
[Gross Profit]
FROM ARLINH
WHERE (((ARLINH.Docno)=[Document No]) AND ((ARLINH.Docdate)=[date]))
ORDER BY ARLINH.Docno;

 

[John Spencer]

A guess is that the type of Document No is being mis-interpreted.

I would try declaring the parameter types. Also I would use something
besides Date as the name of my parameter, since Date is a reserved word.
Assuming that ARLINH.Docno is a text field then I would use.

PARAMETERS [Document No] Text ( 255 ), [What Date] DateTime;
SELECT DISTINCTROW ARLINH.Docno, ARLINH.Location, ARLINH.Custkey,
ARLINH.Itemkey, ARLINH.Unitprice, ARLINH.Ordqty, [Unitprice]*[Ordqty] AS
[Sale Amount], ARLINH.Docdate, ARLINH.Cost, [Sale Amount]-[Cost]*[Ordqty] AS
[Gross Profit]
FROM ARLINH
WHERE (((ARLINH.Docno)=[Document No]) AND ((ARLINH.Docdate)=[What date]))
ORDER BY ARLINH.Docno;

If ARLINH.Docno is a number field then the parameter declaration should
probably be more like
PARAMETERS [Document No] IeeeDouble, [What Date] DateTime;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..