Querying Dates in Access 2000

Hello,

I'm connecting to an Access 2000 via the Microsoft ODBC driver.  I'm able to
connect to database and query records in string fields, however, I cannot
query date fields due to errors or incorrect query results.  I'm trying to
perform the query in code using Parameters; however, the code below gets the
following error:

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
query1.sql.clear;
if srchcriteria.ItemIndex = 1 then
 begin
  Query1.SQL.Add('select * from applicantdata applicantdata where (LastName
= :Param1) and (DOB = :Param2)');
  Query1.ParamByName('Param1').AsString := searchname.text;
  Query1.ParamByName('Param2').asDate := strtodate(searchDOB.text); //
01/06/1983
  Query1.Open;
 end
end

Project1 raised exception class EBDEEngineError with General SQL Error.
[Microsoft][ODBC Microsoft Access Driver] Invalid datetime format (null).

The Search Date field is not blank so I'm confused about the null.  Is there
some special way I have to submit dates to Access?  When using the Access
query builder automatically inserts '#'s before an after the date, e.g.,
#1/6/1983#.  Trying to add these values in Delphi generates a type mismatch
error.  Finally, typing:

select * from applicantdata applicantdata where (LastName = "Bennett") and
(DOB=01/06/1983);

Directly into the query editor at design time doesn't generate an error, but
no records are returned - when I know there are two records in the database
with this criteria.

What am I doing wrong?  Any suggestions?
LVL 3
d4jaj1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
hi d4jaj1,

change this line

  Query1.ParamByName('Param2').asDate := strtodate(searchDOB.text); //
01/06/1983
 
to

  Query1.ParamByName('Param2').asDateTime := strtodate(searchDOB.text); //
01/06/1983
 
and try again

meikl
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
d4jaj1Author Commented:
Perfect!!

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.