M
MRFannon
When I create a parameter query in Access ’02 against two datetime
columns…one with date only in it, and one with time only in it, and prompt
the user for start and end/date and time, and the datasource is in SQL Server
‘00, I experience two problems. First, it prompts in the wrong order: End
Time, then Start Date, End Date and Start Time. Second, it returns zero
rows. No matter what I do, if I prompt for start and end TIME, it always
returns zero rows. If I prompt for start and end date only, it works fine.
If I prompt for just date, or just time, it will also prompt in the correct
order. If I create the query in SQL Server using Query Analyzer in
Enterprise Manger, it works fine. If I create an Access database, (.mdb
file), import the data, then create the query against that database, it also
works fine. Below is the query created in SQL Server that works, and the one
created in Access that doesn’t. Please help. Thanks.
Mark
SQL Server query (that works)
SELECT *, General_Start_Date AS Expr1, General_Start_Time AS Expr2,
General_Total_Packets AS Expr3,
General_Average_Utilization_kbitsPERsec AS Expr4
FROM SumStats
WHERE (General_Start_Date BETWEEN %StDt% AND %EdDt%) AND
(General_Start_Time BETWEEN %StTm% AND %EdTm%)
Access query that doesn’t
ALTER FUNCTION dbo.DateAndTimePrompt
(@BegDate datetime,
@EndDate datetime,
@StartTime datetime,
@EndTime datetime)
RETURNS TABLE
AS
RETURN ( SELECT General_Start_Date, General_Start_Time,
General_Total_Packets, General_Average_Utilization_kbitsPERsec
FROM dbo.SumStats
WHERE (General_Start_Date BETWEEN @BegDate AND @EndDate) AND
(General_Start_Time BETWEEN @StartTime AND @EndTime) )
columns…one with date only in it, and one with time only in it, and prompt
the user for start and end/date and time, and the datasource is in SQL Server
‘00, I experience two problems. First, it prompts in the wrong order: End
Time, then Start Date, End Date and Start Time. Second, it returns zero
rows. No matter what I do, if I prompt for start and end TIME, it always
returns zero rows. If I prompt for start and end date only, it works fine.
If I prompt for just date, or just time, it will also prompt in the correct
order. If I create the query in SQL Server using Query Analyzer in
Enterprise Manger, it works fine. If I create an Access database, (.mdb
file), import the data, then create the query against that database, it also
works fine. Below is the query created in SQL Server that works, and the one
created in Access that doesn’t. Please help. Thanks.
Mark
SQL Server query (that works)
SELECT *, General_Start_Date AS Expr1, General_Start_Time AS Expr2,
General_Total_Packets AS Expr3,
General_Average_Utilization_kbitsPERsec AS Expr4
FROM SumStats
WHERE (General_Start_Date BETWEEN %StDt% AND %EdDt%) AND
(General_Start_Time BETWEEN %StTm% AND %EdTm%)
Access query that doesn’t
ALTER FUNCTION dbo.DateAndTimePrompt
(@BegDate datetime,
@EndDate datetime,
@StartTime datetime,
@EndTime datetime)
RETURNS TABLE
AS
RETURN ( SELECT General_Start_Date, General_Start_Time,
General_Total_Packets, General_Average_Utilization_kbitsPERsec
FROM dbo.SumStats
WHERE (General_Start_Date BETWEEN @BegDate AND @EndDate) AND
(General_Start_Time BETWEEN @StartTime AND @EndTime) )