Access time range query w/user input prompt and SQL datasource

[Guest]

If I create a query in an Access Project, (with the datasource in SQL 2000),
and I prompt for a time range, (start and end time), it returns zero rows.
If, instead of prompting for the time range, I explicitly put the time range
in the query, it returns the correct rows. This is only a problem when the
database is in SQL Server, and the query is created in Access. If the query
is created in SQL Server, (Ent. Mngr. and SQL Analyzer), or when the database
is created in Access it works fine. Anything would be helpful. Thanks.

 

[Dale Fye]

It probably has to do with the way you are formatin the date values when you
pass them from Access to SQL Server. Can you post the query that works in
SQL Server, and the one in Access that doesn't?

 

[Guest]

It probably has to do with the way you are formatin the date values when you
pass them from Access to SQL Server. Can you post the query that works in
SQL Server, and the one in Access that doesn't?




Here is the query created in Ent. Mngr. which returns the correct rows

SELECT *, StartDate AS Expr1, StartTime AS Expr2, TotalPackets AS Expr3, [General::Average Utilization (kbits/s)] AS Expr4

FROM SumStats
WHERE (StartTime BETWEEN %STm% AND %ETm%)


Here is the query created in Access that returns zero rows:

ALTER FUNCTION dbo.TmPrmpt
(@STm datetime,
@ETm datetime)
RETURNS TABLE
AS
RETURN ( SELECT StartDate, StartTime, TotalPackets, [General::Average
Utilization (kbits/s)]
FROM dbo.SumStats
WHERE (StartTime BETWEEN @STm AND @ETm) )



Thanks.