• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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?
0
d4jaj1
Asked:
d4jaj1
1 Solution
 
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
 
d4jaj1Author Commented:
Perfect!!

Thanks.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now