Problem of Date with MS Access

[brad]

I am trying to display the records which the date (the Name field is Period) are greater than today's date from MS Access. It works fine except that I cannot display any records for April. Please find the below query and help to resove the problem and many thanks in advance.

reqSQL = "Select EDate,Url, Title, Sdate, Edate, Address, Organiser FROM Train_Events WHERE CDATE(Period) >= #" & getToDate & "#" & " ORDER BY CDATE(Period)"

[John Blessing]

"brad" <anonymous@discussions.microsoft.com> wrote in message
news:4B1CD25D-40A0-4682-9C47-5A226E3F9421@microsoft.com...

> I am trying to display the records which the date (the Name field is

Period) are greater than today's date from MS Access. It works fine except
that I cannot display any records for April. Please find the below query
and help to resove the problem and many thanks in advance.

>
> reqSQL = "Select EDate,Url, Title, Sdate, Edate, Address, Organiser

FROM Train_Events WHERE CDATE(Period) >= #" & getToDate & "#" & " ORDER BY
CDATE(Period)"

If the column "Period" is a date field, there is no need to use the CDATE
function. Also, your "getToDate" must be in the format mm/dd/yyyy

--
John Blessing

[url]http://www.LbeHelpdesk.com[/url] - Help Desk software priced to suit all
businesses
[url]http://www.free-helpdesk.com[/url] - Completely free help desk software !
[url]http://www.lbetoolbox.com[/url] - Remove Duplicates from MS Outlook

[brad]

Thanks for ur reply! I have tried to remove the CDATE but the problem remains. My getToDate format is dd/mm/yyyy and same as the "period" column" Does the date has to be in mm/ddyyy format to make the sorting correct? Pls advise

[John Blessing]

"brad" <anonymous@discussions.microsoft.com> wrote in message
news:5F6A79A3-C8C4-497D-980F-115EBD38B67C@microsoft.com...

> Thanks for ur reply! I have tried to remove the CDATE but the problem

remains. My getToDate format is dd/mm/yyyy and same as the "period" column"
Does the date has to be in mm/ddyyy format to make the sorting correct? Pls
advise

Yes, use mm/dd/yyyy. If the period column is a date column it doesn't have a
'format', it is a date. It's like saying I have numeric column and it's
format is nnn,nnn.nn. Don't confuse the internal storage format of a
datatype with the way your computer presents it to you.

Regards

John Blessing
[url]http://www.LbeHelpdesk.com[/url] - Help Desk software at affordable prices
[url]http://www.free-helpdesk.com[/url] - Completely free help desk software !
[url]http://www.lbetoolbox.com[/url] - Remove Duplicates from MS Outlook

[Aaron Bertrand [MVP]]

> Also, your "getToDate" must be in the format mm/dd/yyyy

Actually, the only safe format to pass to Access is YYYY-MM-DD. Anything
else can be misconstrued based on regional settings, etc... which can change
at any time.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[brad]

Thank you all of your reply
My regional setting has to be dd/mm/yyyy. How can I change the MS access to yyyy/mm/dd in the Period column. there is no such format in the drop down box. Pls advise again. Many thanks

[Aaron Bertrand [MVP]]

Don't worry about the format that's stored within Access. In fact, what you
see in the Access GUI is not what's stored at all.

Worry about how you *present* it to the user, and how you get it into the
database in the first place. You can manipulate the date format in both
directions using VBScript, not some drop-down in the Access GUI...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]





"brad" <anonymous@discussions.microsoft.com> wrote in message
news:8D9258F5-7DC9-4C6D-8C93-24C4966E3D5E@microsoft.com...

> Thank you all of your reply!
> My regional setting has to be dd/mm/yyyy. How can I change the MS access

to yyyy/mm/dd in the Period column. there is no such format in the drop down
box. Pls advise again. Many thanks