date problem in access

[hardik]

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(),vbshortd ate)

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",vbshortda te)
end_date = formatdatetime( "10/09/2006",vbshortda te)

"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

[ruralguy via AccessMonster.com]

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(),vbshort date)

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",vbshortda te)
end_date = formatdatetime( "10/09/2006",vbshortda te)

"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.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1

[Rick Brandt]

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(),vbshortd ate)

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",vbshortda te)
end_date = formatdatetime( "10/09/2006",vbshortda te)

"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