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_AuthFormRecDate<=[Start Date]
AND
clt_A21a_RecordType=10
AND
(
clt_A21_RecordStatus=10 OR (clt_A23_DateClosed>=[Start Date])
)
AND
(
(q01_Clients.clt_A24_Department=10 AND clt_A98_Transferred = FALSE)
OR
(q01_Clients.clt_A24_Department=10 AND clt_A99_DateTransferred

>=[Start Date])

OR
(q01_Clients.clt_A24_Department=20 AND
(clt_A45_ResettlementOpenDate>=[Start Date] OR
clt_A45_ResettlementOpenDate is NULL))
))
OR
(
q01_Clients.clt_A11c_AuthFormRecDate BETWEEN [Start Date] AND [End
Date] AND q01_Clients.clt_A21a_RecordType=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.....ahh 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.net.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....

"Stevienashaa" <st**********@yahoo.co.ukwrote in message
news:11**********************@k35g2000prh.googlegr oups.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_AuthFormRecDate<=[Start Date]
AND
clt_A21a_RecordType=10
AND
(
clt_A21_RecordStatus=10 OR (clt_A23_DateClosed>=[Start Date])
)
AND
(
(q01_Clients.clt_A24_Department=10 AND clt_A98_Transferred = FALSE)
OR
(q01_Clients.clt_A24_Department=10 AND clt_A99_DateTransferred

>>=[Start Date])

OR
(q01_Clients.clt_A24_Department=20 AND
(clt_A45_ResettlementOpenDate>=[Start Date] OR
clt_A45_ResettlementOpenDate is NULL))
))
OR
(
q01_Clients.clt_A11c_AuthFormRecDate BETWEEN [Start Date] AND [End
Date] AND q01_Clients.clt_A21a_RecordType=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