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

date problem in access

P: n/a
hi friends,

i am really surprized the way access behaves in date fields i mean
it's all ok when you have us time zone or us servers but if you have
diffrent timezone like uk then access creates so many problems ....

first of all i try access with uk settings in date/time in field but
access changes date after 09/11 to 11/09 i don't know why so i decided
to take it as text field steel it is creating problems

i have date field in my database

and when user selects two dates i need to find all the date between
those two dates.

currently i have assign text datatype for date field i am storing dates
in this field like

formatdatetime(date(),vbshortdate)

my server is uk based and here is it's date format:: dd-mm-yyyy

now when user searches two dates like 25/06/2006 and 10/09/2006 then
i am writing query like

start_date = formatdatetime("25/06/2006",vbshortdate)
end_date = formatdatetime("10/09/2006",vbshortdate)

"SELECT * FROM table WHERE datefield BETWEEN #"&start_date&"# AND
$"&end_date&""

but access is not searching and after searching diffrent dates i found
sometimes access searches month as day ,

so i change the date field's datatype in database but then when i try
it steel not run
i believe here lots of people experience and some may have feel this
problem plz tell me solution of problem or atleast give me hint of how
to do it.

thanx in advance

Nov 11 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
See if this link helps: http://www.mvps.org/access/datetime/date0005.htm

hardik wrote:
>hi friends,

i am really surprized the way access behaves in date fields i mean
it's all ok when you have us time zone or us servers but if you have
diffrent timezone like uk then access creates so many problems ....

first of all i try access with uk settings in date/time in field but
access changes date after 09/11 to 11/09 i don't know why so i decided
to take it as text field steel it is creating problems

i have date field in my database

and when user selects two dates i need to find all the date between
those two dates.

currently i have assign text datatype for date field i am storing dates
in this field like

formatdatetime(date(),vbshortdate)

my server is uk based and here is it's date format:: dd-mm-yyyy

now when user searches two dates like 25/06/2006 and 10/09/2006 then
i am writing query like

start_date = formatdatetime("25/06/2006",vbshortdate)
end_date = formatdatetime("10/09/2006",vbshortdate)

"SELECT * FROM table WHERE datefield BETWEEN #"&start_date&"# AND
$"&end_date&""

but access is not searching and after searching diffrent dates i found
sometimes access searches month as day ,

so i change the date field's datatype in database but then when i try
it steel not run

i believe here lots of people experience and some may have feel this
problem plz tell me solution of problem or atleast give me hint of how
to do it.

thanx in advance
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200611/1

Nov 11 '06 #2

P: n/a
hardik wrote:
hi friends,

i am really surprized the way access behaves in date fields i mean
it's all ok when you have us time zone or us servers but if you have
diffrent timezone like uk then access creates so many problems ....

first of all i try access with uk settings in date/time in field but
access changes date after 09/11 to 11/09 i don't know why so i decided
to take it as text field steel it is creating problems

i have date field in my database

and when user selects two dates i need to find all the date between
those two dates.

currently i have assign text datatype for date field i am storing
dates in this field like

formatdatetime(date(),vbshortdate)

my server is uk based and here is it's date format:: dd-mm-yyyy

now when user searches two dates like 25/06/2006 and 10/09/2006 then
i am writing query like

start_date = formatdatetime("25/06/2006",vbshortdate)
end_date = formatdatetime("10/09/2006",vbshortdate)

"SELECT * FROM table WHERE datefield BETWEEN #"&start_date&"# AND
$"&end_date&""

but access is not searching and after searching diffrent dates i found
sometimes access searches month as day ,

so i change the date field's datatype in database but then when i try
it steel not run
i believe here lots of people experience and some may have feel this
problem plz tell me solution of problem or atleast give me hint of how
to do it.

thanx in advance
Date literals in Access MUST be either US format or a non-ambiguous format (ISO
or where the month uses alpha characters).
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Nov 11 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.