Date parameters in queries

[Stevienashaa]

Hello

I'm using Access 2003, and I have a query (written in SQL) which has
two parameters and asks the user for two dates. This has been working
fine. Today I modified the query, removing the paramerters and hard-
coding some dates in, in dd/mm/yy format. To my surprise I got a
different number of records returned. I then, as an experiment, change
the date format to dd/mm/yyyy. This again returned a different number
of records, different to both previous instances. When I use the
parameters I get the same result, irrespective of the date format
used. Has anybody come across anything liek this before - I've
searched here and can't see and previous postings, but apologies if
this has been covered before. The query is only using one table, so
it's nothing particularly complicated. The full query (with
parameters) is pasted below.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT q01_Clients.*
FROM q01_Clients
WHERE (
(
q01_Clients.clt _A11c_AuthFormR ecDate<=[Start Date]
AND
clt_A21a_Record Type=10
AND
(
clt_A21_RecordS tatus=10 OR (clt_A23_DateCl osed>=[Start Date])
)
AND
(
(q01_Clients.cl t_A24_Departmen t=10 AND clt_A98_Transfe rred = FALSE)
OR
(q01_Clients.cl t_A24_Departmen t=10 AND clt_A99_DateTra nsferred

>=[Start Date])

OR
(q01_Clients.cl t_A24_Departmen t=20 AND
(clt_A45_Resett lementOpenDate> =[Start Date] OR
clt_A45_Resettl ementOpenDate is NULL))
))
OR
(
q01_Clients.clt _A11c_AuthFormR ecDate BETWEEN [Start Date] AND [End
Date] AND q01_Clients.clt _A21a_RecordTyp e=10
));

Many thanks in anticipation!

Stephen

[John]

When you hard code it in the SQL are you wrapping it in #ddmmyyyy#?

Also, I believe access ALWAYS stores dates in American format, mmddyyyy
regardless of your localization settings.....ah h I found it...heh its even
got a reference to the access legend who I learned it from...

Function SQLDate(varDate As Variant) As String
'Got this from Allen Browne master of Access
'http://users.bigpond.n et.au/abrowne1/ser-36.html
'Access forcess american date formate for all SQL!
'#mm/dd/yyyy hh:mm:ss#
If IsDate(varDate) Then
SQLDate = "#" & Format$(varDate , "mm\/dd\/yyyy") & "#"
End If
End Function

Hope that helps....

"Stevienash aa" <st**********@y ahoo.co.ukwrote in message
news:11******** **************@ k35g2000prh.goo glegroups.com.. .

Hello

I'm using Access 2003, and I have a query (written in SQL) which has
two parameters and asks the user for two dates. This has been working
fine. Today I modified the query, removing the paramerters and hard-
coding some dates in, in dd/mm/yy format. To my surprise I got a
different number of records returned. I then, as an experiment, change
the date format to dd/mm/yyyy. This again returned a different number
of records, different to both previous instances. When I use the
parameters I get the same result, irrespective of the date format
used. Has anybody come across anything liek this before - I've
searched here and can't see and previous postings, but apologies if
this has been covered before. The query is only using one table, so
it's nothing particularly complicated. The full query (with
parameters) is pasted below.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT q01_Clients.*
FROM q01_Clients
WHERE (
(
q01_Clients.clt _A11c_AuthFormR ecDate<=[Start Date]
AND
clt_A21a_Record Type=10
AND
(
clt_A21_RecordS tatus=10 OR (clt_A23_DateCl osed>=[Start Date])
)
AND
(
(q01_Clients.cl t_A24_Departmen t=10 AND clt_A98_Transfe rred = FALSE)
OR
(q01_Clients.cl t_A24_Departmen t=10 AND clt_A99_DateTra nsferred

>>=[Start Date])

OR
(q01_Clients.cl t_A24_Departmen t=20 AND
(clt_A45_Resett lementOpenDate> =[Start Date] OR
clt_A45_Resettl ementOpenDate is NULL))
))
OR
(
q01_Clients.clt _A11c_AuthFormR ecDate BETWEEN [Start Date] AND [End
Date] AND q01_Clients.clt _A21a_RecordTyp e=10
));

Many thanks in anticipation!

Stephen

[Stevienashaa]

Thanks John (and Allen!). I've now recoded the dates the other way
around (they were already in #'s) and it works a treat. It's obvious
when you know - I'd thought I'd changed all date settings to UK
format, but I didn't realise that it did this - so thanks very much
for taking the time to sort this for me.

Stephen