🚀 go-pugleaf

ADO components replace :ParamName with ? in actual query. Try Access
specific syntax:

Parameters SDate DateTime, EDate DateTime;
 SELECT
    a.[Name],
    a.[StartDate],
    a.[EndDate],
    a.[Hours],
    (SELECT SUM([Hours]) FROM [ATable] AS b WHERE b.[StartDate] >= SDate AND
 b.[EndDate] <= EDate) AS Total
 FROM [ATable] AS a
 WHERE a.[StartDate] >= SDate AND a.[EndDate] <= EDate ;

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi + ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)

"Freshy" <_REMOVE_THIS_freshy_s@hotmail.com> �������/�������� � ��������
���������: news:40034ae2$1@newsgroups.borland.com...
> Hello,
>
> I have a query to run against an Access 2000 database for a report where I
> need to use a parameter twice.  For example:
>
> SELECT
>    a.[Name],
>    a.[StartDate],
>    a.[EndDate],
>    a.[Hours],
>    (SELECT SUM([Hours]) FROM [ATable] AS b WHERE b.[StartDate] >= :SDate
AND
> b.[EndDate] <= :EDate) AS Total
> FROM [ATable] AS a
> WHERE a.[StartDate] >= :SDate AND a.[EndDate] <= :EDate ;
>
> I know that ADO has a problem with this, so I tried a couple of
variations:
>
> SELECT
>    a.[Name],
>    a.[StartDate],
>    a.[EndDate],
>    a.[Hours],
>    :SDate AS PSDate,
>    :EDate AS PEDate,
>    (SELECT SUM([Hours]) FROM [ATable] AS b WHERE b.[StartDate] >= PSDate
AND
> b.[EndDate] <= PEDate) AS Total
> FROM [ATable] AS a
> WHERE a.[StartDate] >= PSDate AND a.[EndDate] <= PEDate ;
>
> Unfortunately, this did not work.  Does anyone know of a way around this?
>
> Thanks,
> Ken
>
>