 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
John Evans
Guest
|
 Posted: Tue Dec 30, 2003 4:46 pm Post subject: date handling between Sql server and Access |
 |
|
Dear all,
I have datetimepickers ("dtfrom" and "dtTo") on a form to
select "from-To" date range for my reports.
In my query I have something like
where table.somedate>:pfromdate
and table.somedate<:ptodate
when I use the query I do:
Qmanservice.close;
QManservice.Parameters.ParamValues['pfromdate']:=trunc(dtfrom.DateTime);
QManservice.Parameters.ParamValues['ptodate']:=trunc(dtto.DateTime)+0.999;
Qmanservice.open;
I have been using this quite happily in MS Sql Server 2000. I now want to do
the same thing in Access, and there is an error when I open the query of
"Data type mismatch in criteria expression".
I am using ADO for both. I am using D6 ent on win2000.
Any ideas?
thanks
--
John Evans
Managing Director
Clear Advantage Ltd
www.Clear-Advantage.co.uk
|
|
| Back to top |
|
 |
Tony J Hopkinson
Guest
|
| Posted: Tue Dec 30, 2003 10:13 pm Post subject: Re: date handling between Sql server and Access |
|
|
On Tue, 30 Dec 2003 16:46:45 -0000, "John Evans"
<johnevans (AT) clear-Advantage (DOT) co.uk> wrote:
| Quote: | Dear all,
I have datetimepickers ("dtfrom" and "dtTo") on a form to
select "from-To" date range for my reports.
In my query I have something like
where table.somedate>:pfromdate
and table.somedate<:ptodate
when I use the query I do:
Qmanservice.close;
QManservice.Parameters.ParamValues['pfromdate']:=trunc(dtfrom.DateTime);
QManservice.Parameters.ParamValues['ptodate']:=trunc(dtto.DateTime)+0.999;
Qmanservice.open;
I have been using this quite happily in MS Sql Server 2000. I now want to do
the same thing in Access, and there is an error when I open the query of
"Data type mismatch in criteria expression".
I am using ADO for both. I am using D6 ent on win2000.
Any ideas?
thanks
|
well trunc returns an integer, access doesn't think an integer is a
date.
Access likes dates
Select * from MyTable Whete myDate = #19/12/03# or most other formats
or as strings.
If you want common code that works for both, use strings for the dates
wrapped in quotes and use FormatDateTime to put them in a recognisable
format.
I always use strings for dates, worked for me since 1996 and no one's
given me a sensible reason to change yet.
|
|
| Back to top |
|
|
John Evans
Guest
|
| Posted: Wed Dec 31, 2003 11:17 am Post subject: Re: date handling between Sql server and Access |
|
|
Dear Brian,
sorted!
I was looking in the wrong place. Just seeing your response made me realise
my mistake, funny how that happens.
Many thanks
John
"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote
| Quote: |
I have been using this quite happily in MS Sql Server 2000. I now want to
do
the same thing in Access, and there is an error when I open the query of
"Data type mismatch in criteria expression".
The error sounds like the datatype of the parameters you are using with
the
Access version are different from what is defined in the SQL version.
Are you creating the query in your code or in the VCL?
Are you using the Jet Oledb provider?
--
Brian Bushay (TeamB)
[email]Bbushay (AT) NMPLS (DOT) com[/email]
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
