By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Date parameters in queries

P: n/a
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

Oct 12 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Oct 14 '07 #2

P: n/a
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

Oct 15 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.