Board index » delphi » Passing Date Parameter Using Access 97?

Passing Date Parameter Using Access 97?

How does one pass a date as a parameter using the SQL query component?  If
I hard code the SQL, using #06/01/98# in the 'where' clause', BDE will set
the query and retreive records, but if I include the standard syntax
:mydate in the 'where' clause as a parameter, I cannot use #06/01/98# as a
date/time or date type parameter, since #/06/01/98# is a string value.
Without the '#''s, Access '97 is not happy and will not retrieve records.
Anyone?

 

Re:Passing Date Parameter Using Access 97?


Quote
Preferred Customer wrote in message

<01bd9097$c44416e0$3e2e7...@pg.usr.com>...

Quote
>How does one pass a date as a parameter using the SQL query component?  If
>I hard code the SQL, using #06/01/98# in the 'where' clause', BDE will set
>the query and retreive records, but if I include the standard syntax
>:mydate in the 'where' clause as a parameter, I cannot use #06/01/98# as a
>date/time or date type parameter, since #/06/01/98# is a string value.
>Without the '#''s, Access '97 is not happy and will not retrieve records.
>Anyone?

Something like sqlStr := 'SELECT * FROM [myTable] WHERE [myTable].[myDate] =
#' + formatDateTime('mm/dd/yyyy',dtVar) + '#';

has always worked for me. Is that what you mean. If it's not, drop me a
line.

By the way, if you're using ACCESS with BDE, could I urge that you junk the
BDE and use one of the DAO components. You'll be pleased you did!

pie...@finalfiler.com

Re:Passing Date Parameter Using Access 97?


Quote
>but if I include the standard syntax
>:mydate in the 'where' clause as a parameter, I cannot use #06/01/98# as a
>date/time or date type parameter, since #/06/01/98# is a string value.
>Without the '#''s, Access '97 is not happy and will not retrieve records.
>Anyone?

Make the paramater a string.

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Re:Passing Date Parameter Using Access 97?


Brian, when I set the parameter value cldate to string, I get an SQL return
error 'datatype mismatch'?
Here is the sql:

SELECT ClassHst.*
FROM ClassHst
WHERE (((ClassHst.CLASSDATE)=:cldate));

ClassDate is a date/time within Access '97.  Am I still missing something
here?

Brian Bushay TeamB <BBus...@DataGuidance.com> wrote in article
<3582921c.44985...@forums.borland.com>...

Quote
> Make the paramater a string.

> --
> Brian Bushay (TeamB)
> Bbus...@DataGuidance.com

Re:Passing Date Parameter Using Access 97?


Pat

I did some testing on this and you do seem to have to match the value to the
type of the paramater.  So you can use  dates bracketed with # like Access does
in paramaters.  That leaves you with constructing the SQL statement in Code.
or
in my testing I did not need the # bracets if I used a paramater of the dateTime
type and just asigend it a dateTime value.

Quote
>Brian, when I set the parameter value cldate to string, I get an SQL return
>error 'datatype mismatch'?
>Here is the sql:

>SELECT ClassHst.*
>FROM ClassHst
>WHERE (((ClassHst.CLASSDATE)=:cldate));

>ClassDate is a date/time within Access '97.  Am I still missing something
>here?

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Other Threads