Re: DateTime parameters in ADOQuery

Alan,

This is what I would do:
(at design time using the TADODataSet)
Set the CommandText to:
Select *
From Employee
Where DateHired BETWEEN :StartRange AND :EndRange

The corresponding parameters should be automatically configured.

In Delphi code (*Note: Code is off the top of my head, so there are probably
syntax errors)

public
function DoesEmployeeDateRangeExist(const StartDate, EndDate
:TDateTime):Boolean;
end;

function TMyBusinessObjectOrForm.DoesEmployeeDateRangeExist(const StartDate,
EndDate :TDateTime):Boolean;
begin
with qryEmployee do
begin { Start of with ADO query/dataset do }
DisableControls();
try
try { Start of try...except block }
Close();
Parameters.ParamByName('StartRange').Value :=
FormatDateTime('mm/dd/yyyy',StartDate); //start at midnight
Parameters.ParamByName('EndRange').Value :=
FormatDateTime('mm/dd/yyyy',EndDate) + ' 11:59PM'; //capture the entire day
Open();
Result := NOT(IsEmpty);
except
on e: Exception do
begin
Result := False;
//Set an error message property, re-raise the exception with a
more user friendly message, etc
end;
end; { End of try...except block }
finally
EnableControls();
end;
end; { End of with ado query/dataset do }
end;

In the UI
{some button click}
begin
if
(MyBusinessObjectOrForm.DoesEmployeeDateRangeExist(dtThisMonthStocktakeDate,
dtNextMonthStocktakeDate)) then
begin
//It worked
end
else
begin
ShowMessage('No Employee');
end;
end;

"Alan T" <alanpltseNOSPAM@xxxxxxxxxxxx> wrote in message
news:465b9c55@xxxxxxxxxxxxxxxxxxxxxxxxx
I got a strange problem about the DateTime field in MSAccess.

A field in the Access table defined as DateTime, I have the SQL in
ADOQuery
SELECT *
FROM Employee
WHERE (DateHired > :StartRange)
AND (DateHired <= :EndRange)

This is my code:
QryEmployee.Close;
QryEmployee.Parameters.ParamByName(StartRange).Value :=
dtThisMonthStocktakeDate;
QryEmployee.Parameters.ParamByName(EndRange).Value :=
dtNextMonthStocktakeDate;
QryEmployee.Open;
if QryEmployee.Eof then
showmessage('no employee');

I found that it always return no employee.
I also checked that the parameter value I passed are correct, eg
1/03/2007 and 1/06/2007

It should have employee return by checking the tables it has employee in
this range.

Any idea?



.

Quantcast