date

[Bob Barrows]

Nigel Taylor wrote:

> When i update a record in a Access database the date field converts
> from english to american and then amercian to english and keeps
> changing between the two, i am able to insert the date in english
> format.
>
> I am running a win 2k3 server and the locale date and time settings
> are english and have set the lcid to 2057.
>
> Does anybodu know why this changes?
>
> Nigel

From the JetSQL Reference in Access online help:
*********************************
When you specify the criteria argument, date literals must be in U.S.
format, even if you are not using the U.S. version of the Microsoft® Jet
database engine. For example, May 10, 1996, is written 10/5/96 in the United
Kingdom and 5/10/96 in the United States. Be sure to enclose your date
literals with the number sign (#) as shown in the following examples.
To find records dated May 10, 1996 in a United Kingdom database, you must
use the following SQL statement:
SELECT *
FROM Orders
WHERE ShippedDate = #5/10/96#;
You can also use the DateValue function which is aware of the international
settings established by Microsoft Windows®. For example, use this code for
the United States:
SELECT *
FROM Orders
WHERE ShippedDate = DateValue('5/10/96');
And use this code for the United Kingdom:
SELECT *
FROM Orders
WHERE ShippedDate = DateValue('10/5/96');
*********************************
Dates and times are stored in Jet as numbers of type Double. No format. The
whole number portion contains the number of days since the seed date. The
decimal portion is the fractional part of a day: .5 = 12:00 noon.

HTH,
Bob Barrows

[Nigel Taylor]

Thanks for the quick response. Above is the date of the SQL statement which
i am using to insert the dates into the database.

"fldDate_of_Application = #" & request("DoA") & "#, " &_
"fldDOB = #" & request("DoB") & "#"

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:OGr7JIRWDHA.2256@TK2MSFTNGP10.phx.gbl...

> Nigel Taylor wrote:

> > When i update a record in a Access database the date field converts
> > from english to american and then amercian to english and keeps
> > changing between the two, i am able to insert the date in english
> > format.
> >
> > I am running a win 2k3 server and the locale date and time settings
> > are english and have set the lcid to 2057.
> >
> > Does anybodu know why this changes?
> >
> > Nigel

>
> From the JetSQL Reference in Access online help:
> *********************************
> When you specify the criteria argument, date literals must be in U.S.
> format, even if you are not using the U.S. version of the Microsoft® Jet
> database engine. For example, May 10, 1996, is written 10/5/96 in the

United

> Kingdom and 5/10/96 in the United States. Be sure to enclose your date
> literals with the number sign (#) as shown in the following examples.
> To find records dated May 10, 1996 in a United Kingdom database, you must
> use the following SQL statement:
> SELECT *
> FROM Orders
> WHERE ShippedDate = #5/10/96#;
> You can also use the DateValue function which is aware of the

international

> settings established by Microsoft Windows®. For example, use this code for
> the United States:
> SELECT *
> FROM Orders
> WHERE ShippedDate = DateValue('5/10/96');
> And use this code for the United Kingdom:
> SELECT *
> FROM Orders
> WHERE ShippedDate = DateValue('10/5/96');
> *********************************
> Dates and times are stored in Jet as numbers of type Double. No format.

The

> whole number portion contains the number of days since the seed date. The
> decimal portion is the fractional part of a day: .5 = 12:00 noon.
>
> HTH,
> Bob Barrows
>
>
>

[Foo Man Chew]

> 2) Hmm ... I had two points to make but now I can't remember the other one

Probably something about making sure dates are passed in YYYY-MM-DD format,
rather than however the user decides to format them...

[Ken Schaefer]

Date Literals can be in ISO style format as well, and Jet will happily
accept them.

Cheers
Ken

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:OGr7JIRWDHA.2256@TK2MSFTNGP10.phx.gbl...
: Nigel Taylor wrote:
: > When i update a record in a Access database the date field converts
: > from english to american and then amercian to english and keeps
: > changing between the two, i am able to insert the date in english
: > format.
: >
: > I am running a win 2k3 server and the locale date and time settings
: > are english and have set the lcid to 2057.
: >
: > Does anybodu know why this changes?
: >
: > Nigel
:
: From the JetSQL Reference in Access online help:
: *********************************
: When you specify the criteria argument, date literals must be in U.S.
: format, even if you are not using the U.S. version of the Microsoft® Jet
: database engine. For example, May 10, 1996, is written 10/5/96 in the
United
: Kingdom and 5/10/96 in the United States. Be sure to enclose your date
: literals with the number sign (#) as shown in the following examples.
: To find records dated May 10, 1996 in a United Kingdom database, you must
: use the following SQL statement:
: SELECT *
: FROM Orders
: WHERE ShippedDate = #5/10/96#;
: You can also use the DateValue function which is aware of the
international
: settings established by Microsoft Windows®. For example, use this code for
: the United States:
: SELECT *
: FROM Orders
: WHERE ShippedDate = DateValue('5/10/96');
: And use this code for the United Kingdom:
: SELECT *
: FROM Orders
: WHERE ShippedDate = DateValue('10/5/96');
: *********************************
: Dates and times are stored in Jet as numbers of type Double. No format.
The
: whole number portion contains the number of days since the seed date. The
: decimal portion is the fractional part of a day: .5 = 12:00 noon.
:
: HTH,
: Bob Barrows
:
:
:

[Bob Barrows]

Have you tried it? I've never been able to get Jet to recognize a date in
ISO format (YYYYMMDD).

Ken Schaefer wrote:

> Date Literals can be in ISO style format as well, and Jet will happily
> accept them.
>
> Cheers
> Ken
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:OGr7JIRWDHA.2256@TK2MSFTNGP10.phx.gbl...

>> Nigel Taylor wrote:

>>> When i update a record in a Access database the date field converts
>>> from english to american and then amercian to english and keeps
>>> changing between the two, i am able to insert the date in english
>>> format.
>>>
>>> I am running a win 2k3 server and the locale date and time settings
>>> are english and have set the lcid to 2057.
>>>
>>> Does anybodu know why this changes?
>>>
>>> Nigel

>>
>> From the JetSQL Reference in Access online help:
>> *********************************
>> When you specify the criteria argument, date literals must be in U.S.
>> format, even if you are not using the U.S. version of the Microsoft®
>> Jet database engine. For example, May 10, 1996, is written 10/5/96
>> in the United Kingdom and 5/10/96 in the United States. Be sure to
>> enclose your date literals with the number sign (#) as shown in the
>> following examples.
>> To find records dated May 10, 1996 in a United Kingdom database, you
>> must use the following SQL statement:
>> SELECT *
>> FROM Orders
>> WHERE ShippedDate = #5/10/96#;
>> You can also use the DateValue function which is aware of the
>> international settings established by Microsoft Windows®. For
>> example, use this code for the United States:
>> SELECT *
>> FROM Orders
>> WHERE ShippedDate = DateValue('5/10/96');
>> And use this code for the United Kingdom:
>> SELECT *
>> FROM Orders
>> WHERE ShippedDate = DateValue('10/5/96');
>> *********************************
>> Dates and times are stored in Jet as numbers of type Double. No
>> format. The whole number portion contains the number of days since
>> the seed date. The decimal portion is the fractional part of a day:
>> .5 = 12:00 noon.
>>
>> HTH,
>> Bob Barrows

[Nigel Taylor]

Cheers guys thanks for the responses.

I have formatted the date to be updated in american and it is working fine
now.


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:%23CZbIdmWDHA.3612@tk2msftngp13.phx.gbl...

> Have you tried it? I've never been able to get Jet to recognize a date in
> ISO format (YYYYMMDD).
>
> Ken Schaefer wrote:

> > Date Literals can be in ISO style format as well, and Jet will happily
> > accept them.
> >
> > Cheers
> > Ken
> >
> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> > news:OGr7JIRWDHA.2256@TK2MSFTNGP10.phx.gbl...

> >> Nigel Taylor wrote:
> >>> When i update a record in a Access database the date field converts
> >>> from english to american and then amercian to english and keeps
> >>> changing between the two, i am able to insert the date in english
> >>> format.
> >>>
> >>> I am running a win 2k3 server and the locale date and time settings
> >>> are english and have set the lcid to 2057.
> >>>
> >>> Does anybodu know why this changes?
> >>>
> >>> Nigel
> >>
> >> From the JetSQL Reference in Access online help:
> >> *********************************
> >> When you specify the criteria argument, date literals must be in U.S.
> >> format, even if you are not using the U.S. version of the Microsoft®
> >> Jet database engine. For example, May 10, 1996, is written 10/5/96
> >> in the United Kingdom and 5/10/96 in the United States. Be sure to
> >> enclose your date literals with the number sign (#) as shown in the
> >> following examples.
> >> To find records dated May 10, 1996 in a United Kingdom database, you
> >> must use the following SQL statement:
> >> SELECT *
> >> FROM Orders
> >> WHERE ShippedDate = #5/10/96#;
> >> You can also use the DateValue function which is aware of the
> >> international settings established by Microsoft Windows®. For
> >> example, use this code for the United States:
> >> SELECT *
> >> FROM Orders
> >> WHERE ShippedDate = DateValue('5/10/96');
> >> And use this code for the United Kingdom:
> >> SELECT *
> >> FROM Orders
> >> WHERE ShippedDate = DateValue('10/5/96');
> >> *********************************
> >> Dates and times are stored in Jet as numbers of type Double. No
> >> format. The whole number portion contains the number of days since
> >> the seed date. The decimal portion is the fractional part of a day:
> >> .5 = 12:00 noon.
> >>
> >> HTH,
> >> Bob Barrows

>
>
>

[Ken Schaefer]

I use "ISO Style" all the time, but not plain ISO without delimiters. I use:

yyyy/mm/dd

in every project that's used Access for the last 5 years or so (especially
since I'm in Australia, and everyone uses British style dates here). It's
saved me any number of headaches!

Cheers
Ken


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:%23CZbIdmWDHA.3612@tk2msftngp13.phx.gbl...
: Have you tried it? I've never been able to get Jet to recognize a date in
: ISO format (YYYYMMDD).
:
: Ken Schaefer wrote:
: > Date Literals can be in ISO style format as well, and Jet will happily
: > accept them.
: >
: > Cheers
: > Ken
: >
: > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
: > news:OGr7JIRWDHA.2256@TK2MSFTNGP10.phx.gbl...
: >> Nigel Taylor wrote:
: >>> When i update a record in a Access database the date field converts
: >>> from english to american and then amercian to english and keeps
: >>> changing between the two, i am able to insert the date in english
: >>> format.
: >>>
: >>> I am running a win 2k3 server and the locale date and time settings
: >>> are english and have set the lcid to 2057.
: >>>
: >>> Does anybodu know why this changes?
: >>>
: >>> Nigel
: >>
: >> From the JetSQL Reference in Access online help:
: >> *********************************
: >> When you specify the criteria argument, date literals must be in U.S.
: >> format, even if you are not using the U.S. version of the Microsoft®
: >> Jet database engine. For example, May 10, 1996, is written 10/5/96
: >> in the United Kingdom and 5/10/96 in the United States. Be sure to
: >> enclose your date literals with the number sign (#) as shown in the
: >> following examples.
: >> To find records dated May 10, 1996 in a United Kingdom database, you
: >> must use the following SQL statement:
: >> SELECT *
: >> FROM Orders
: >> WHERE ShippedDate = #5/10/96#;
: >> You can also use the DateValue function which is aware of the
: >> international settings established by Microsoft Windows®. For
: >> example, use this code for the United States:
: >> SELECT *
: >> FROM Orders
: >> WHERE ShippedDate = DateValue('5/10/96');
: >> And use this code for the United Kingdom:
: >> SELECT *
: >> FROM Orders
: >> WHERE ShippedDate = DateValue('10/5/96');
: >> *********************************
: >> Dates and times are stored in Jet as numbers of type Double. No
: >> format. The whole number portion contains the number of days since
: >> the seed date. The decimal portion is the fractional part of a day:
: >> .5 = 12:00 noon.
: >>
: >> HTH,
: >> Bob Barrows
:
:
:

[Bob Barrows]

Ah! That's different :-)

Ken Schaefer wrote:

> I use "ISO Style" all the time, but not plain ISO without delimiters.
> I use:
>
> yyyy/mm/dd
>
> in every project that's used Access for the last 5 years or so
> (especially since I'm in Australia, and everyone uses British style
> dates here). It's saved me any number of headaches!
>
> Cheers
> Ken
>
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:%23CZbIdmWDHA.3612@tk2msftngp13.phx.gbl...

>> Have you tried it? I've never been able to get Jet to recognize a
>> date in ISO format (YYYYMMDD).
>>
>> Ken Schaefer wrote:

>>> Date Literals can be in ISO style format as well, and Jet will
>>> happily accept them.
>>>
>>> Cheers
>>> Ken
>>>
>>> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
>>> news:OGr7JIRWDHA.2256@TK2MSFTNGP10.phx.gbl...
>>>> Nigel Taylor wrote:
>>>>> When i update a record in a Access database the date field
>>>>> converts from english to american and then amercian to english
>>>>> and keeps changing between the two, i am able to insert the date
>>>>> in english format.
>>>>>
>>>>> I am running a win 2k3 server and the locale date and time
>>>>> settings are english and have set the lcid to 2057.
>>>>>
>>>>> Does anybodu know why this changes?
>>>>>
>>>>> Nigel
>>>>
>>>> From the JetSQL Reference in Access online help:
>>>> *********************************
>>>> When you specify the criteria argument, date literals must be in
>>>> U.S. format, even if you are not using the U.S. version of the
>>>> Microsoft® Jet database engine. For example, May 10, 1996, is
>>>> written 10/5/96
>>>> in the United Kingdom and 5/10/96 in the United States. Be sure to
>>>> enclose your date literals with the number sign (#) as shown in the
>>>> following examples.
>>>> To find records dated May 10, 1996 in a United Kingdom database,
>>>> you must use the following SQL statement:
>>>> SELECT *
>>>> FROM Orders
>>>> WHERE ShippedDate = #5/10/96#;
>>>> You can also use the DateValue function which is aware of the
>>>> international settings established by Microsoft Windows®. For
>>>> example, use this code for the United States:
>>>> SELECT *
>>>> FROM Orders
>>>> WHERE ShippedDate = DateValue('5/10/96');
>>>> And use this code for the United Kingdom:
>>>> SELECT *
>>>> FROM Orders
>>>> WHERE ShippedDate = DateValue('10/5/96');
>>>> *********************************
>>>> Dates and times are stored in Jet as numbers of type Double. No
>>>> format. The whole number portion contains the number of days since
>>>> the seed date. The decimal portion is the fractional part of a day:
>>>> .5 = 12:00 noon.
>>>>
>>>> HTH,
>>>> Bob Barrows

[paulwgrove webforumsuser@macromedia.com]

Hi there,

I'm having trouble with some lingo. The aim of this is to make the score go to a different frame depending on the date of my computer. Here's what I've got so far, but it seems to be not working any suggestions where i'm going wrong;


on exitFrame me
put the date into field "date"
if char 1 to 4 of the date = "1/1/" then go to frame "3"


help!
pwg
on exitFrame me
put the date into field "date"
if char 1 to 4 of the date = "1/1/" then go to frame "3"

[Chunick webforumsuser@macromedia.com]

try this:

on exitFrame me
put the date into field "date"
if the text of member "date" contains "8/19/" then
go to frame 10
else
go the frame
end if
end

[Andrew Morton]

> on exitFrame me

> put the date into field "date"
> if char 1 to 4 of the date = "1/1/" then go to frame "3"

You absolutely cannot rely on the format of the date. Use the systemDate instead
like this:-

if (the systemDate).month=1 and (the systemDate).day=1 then go "NewYearsDay"

Notice I used a meaningful name for the frame rather than the string "3" - I
suspect you meant the number 3.

HTH

Andrew

[Luke]

Hi

You're better off using the SystemDate object since different computers will
format "the date" differently (apparently, some strange countries put the
date in the bizarre order "month/day/year", for example).

You can use the date object like this

on exitframe (me)
today = the SystemDate
if today.month = 1 AND today.day = 1 then go to frame 3
etc

end

Luke




"paulwgrove" <webforumsuser@macromedia.com> wrote in message
news:bhu3nd$pvu$1@forums.macromedia.com...

> Hi there,
>
> I'm having trouble with some lingo. The aim of this is to make the score

go to a different frame depending on the date of my computer. Here's what
I've got so far, but it seems to be not working any suggestions where i'm
going wrong;

>
>
> on exitFrame me
> put the date into field "date"
> if char 1 to 4 of the date = "1/1/" then go to frame "3"
>
>
> help!
> pwg
> on exitFrame me
> put the date into field "date"
> if char 1 to 4 of the date = "1/1/" then go to frame "3"
>
>

[paulgrove1 webforumsuser@macromedia.com]

Hi,

Thanks for all the help, managed to get it to work with my program, good suggestion of using the system date!

thanks again for the quick responses!

pwg

[yash]

hi,
when building a query I want to have an output
where the date is now() - 7
so it give last week output

how can I do this via the query screen?

please advise
kisoen

[RobGT]

You will need to use the advanced recordset builder and you need to learn
about the DateAdd function of SQL.
Your SQL statement will end up something like this:

SELECT columns
FROM table
WHERE datecolumn >= DATEADD(day, -7, getdate())

HTH
Rob
[url]http://robgt.com[/url]

[rajesh]

this is correct
don't know much about SQL statement like datepart
but is getdate() a function?
if so what is the function?
please advise
kisoen



"RobGT" <rob@lighthouseuk.removeme.net> wrote in message
news:cvurra$ifn$1@forums.macromedia.com...

> You will need to use the advanced recordset builder and you need to learn
> about the DateAdd function of SQL.
> Your SQL statement will end up something like this:
>
> SELECT columns
> FROM table
> WHERE datecolumn >= DATEADD(day, -7, getdate())
>
> HTH
> Rob
> [url]http://robgt.com[/url]
>
>

[RobGT]

Hi,
Sorry for the confusion.
getdate() is a function of SQL Server - It's the equivalent of vbscripts
Now() function, returning the current date and time.

HTH
Rob
[url]http://robgt.com[/url]

[rajesh]

hi,
I have made a search form
with 2 text boxes
these text boxes you should filled a date like from - to
so
textbox1 = vartxt1
textbox2 = vartxt2

how can this sql fit so that filling the 2 textboxes i get the info?

please advise
kisoen

with the beneath sql is not working
i am i doing something wrong?


SELECT Count(Call.CallID) AS CountOfCallID, tblcontype.typedescription
FROM Call INNER JOIN tblcontype ON Call.FunORCorr_change = tblcontype.typeid
WHERE intakedate >= vartxt1
GROUP BY tblcontype.typedescription

[rajesh]

help
"rajesh" <r.kisoenpersad@chello.nl> wrote in message news:...

> hi,
> I have made a search form
> with 2 text boxes
> these text boxes you should filled a date like from - to
> so
> textbox1 = vartxt1
> textbox2 = vartxt2
>
> how can this sql fit so that filling the 2 textboxes i get the info?
>
> please advise
> kisoen
>
> with the beneath sql is not working
> i am i doing something wrong?
>
>
> SELECT Count(Call.CallID) AS CountOfCallID, tblcontype.typedescription
> FROM Call INNER JOIN tblcontype ON Call.FunORCorr_change =
> tblcontype.typeid
> WHERE intakedate >= vartxt1
> GROUP BY tblcontype.typedescription
>

[kt03]

I have a column startdate from the requestor table. I used the (getdate()) to
get the date for startdate.
I want a query to return only the data for currentdate within the current hour
for 3 hrs .
suppose, I beging add the data at 2. Below is the data from the database,
please fill in the where condition , Thanks

select startatedate
from requestor
where startdate ???

2005-07-21 7:28:00
2005-07-21 7:29:00
2005-07-21 9:33:00
2005-07-21 02:33:00
2005-07-21 02:34:00
2005-07-21 05:05:00
2005-07-21 05:18:00