Access, ASP, DATE problems!

[Neil]

I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.

I have had no end of problems and possible solutions to dates such as 1/8/05
being interpreted in the wrong way.

I have been given many suggestions (setting LCID, submitting dates in long
format etc. etc.)

Is there any definitive sloution or tutorial available? This is driving me
MAD!

I can get dates submitted OK but they get read back wrong. I alter the code
and then they are submited wrong but read OK from the database.

Someone please help!!!!!!!!!!!!!!!!
Neil

[RoyVidar]

Neil wrote in message <dc**********@newsg1.svr.pol.co.uk> :

I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.

I have had no end of problems and possible solutions to dates such as 1/8/05
being interpreted in the wrong way.

I have been given many suggestions (setting LCID, submitting dates in long
format etc. etc.)

Is there any definitive sloution or tutorial available? This is driving me
MAD!

I can get dates submitted OK but they get read back wrong. I alter the code
and then they are submited wrong but read OK from the database.

Someone please help!!!!!!!!!!!!!!!!
Neil

Dates are stored as numbers, and displayed as dates. I'm assuming
you are communicating with the Jet engine through dynamic sql, which
is probably the only time this causes challenges, cause the Jet engine
needs the string representation of the date in an unambiguous format
to be able to determine whether 01/08/2005 is Aug 1 or Jan 8.

You will find some more info/suggestions here
http://allenbrowne.com/ser-36.html
http://www.mvps.org/access/datetime/date0005.htm

--
Roy-Vidar

[Neil]

Thanks for the reply

Yes I am using the JET engine.

One of your links says

"In all versions of Access (including the 16-bit versions), JET SQL clauses
require dates in American format. To demonstrate this, enter any date in the
Criteria row under a date field in Query Design, and then switch to SQL
View. In Query Design view, you see the date according to your local
settings, but the SQL statement uses mm/dd/yy format."

My code is successfully submitting dates (they appear in Access as
xx-MMMM-YY, i.e. if the date is submitted as 1/8/05 then access reports it
as 1-AUG-05, which is correct) but when I read them back through code they
are wrong (i.e. 8-Jan-05). What do I use in my VB code to read them correct?

I am really confused by all this!

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...

Neil wrote in message <dc**********@newsg1.svr.pol.co.uk> :

I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.

I have had no end of problems and possible solutions to dates such as
1/8/05
being interpreted in the wrong way.

I have been given many suggestions (setting LCID, submitting dates in
long
format etc. etc.)

Is there any definitive sloution or tutorial available? This is driving
me
MAD!

I can get dates submitted OK but they get read back wrong. I alter the
code
and then they are submited wrong but read OK from the database.

Someone please help!!!!!!!!!!!!!!!!
Neil

Dates are stored as numbers, and displayed as dates. I'm assuming
you are communicating with the Jet engine through dynamic sql, which
is probably the only time this causes challenges, cause the Jet engine
needs the string representation of the date in an unambiguous format
to be able to determine whether 01/08/2005 is Aug 1 or Jan 8.

You will find some more info/suggestions here
http://allenbrowne.com/ser-36.html
http://www.mvps.org/access/datetime/date0005.htm

--
Roy-Vidar

[RoyVidar]

Neil wrote in message <dc**********@newsg3.svr.pol.co.uk> :

Thanks for the reply

Yes I am using the JET engine.

One of your links says

"In all versions of Access (including the 16-bit versions), JET SQL clauses
require dates in American format. To demonstrate this, enter any date in the
Criteria row under a date field in Query Design, and then switch to SQL View.
In Query Design view, you see the date according to your local settings, but
the SQL statement uses mm/dd/yy format."

My code is successfully submitting dates (they appear in Access as
xx-MMMM-YY, i.e. if the date is submitted as 1/8/05 then access reports it as
1-AUG-05, which is correct) but when I read them back through code they are
wrong (i.e. 8-Jan-05). What do I use in my VB code to read them correct?

I am really confused by all this!

Sounds strange. In VBA I'd could perhaps do something like this on an
open
ADO connection, and assign to an ADO recordset

cn.execute "insert into mytable (mytext, mydate) values ('blah', #" & _
format$(date,"yyyy-mm-dd") & "#)",,adcmdtext+adexecutenorecords
set rs=cn.execute("select * from mytable where mytext =
'blah'",,adcmdtext)
debug.print rs.getstring

on my setups (with Access/VBA/Norwegian settings), I'd get the same
date I
put in, as long as the date is stored as a date/time, at least.

--
Roy-Vidar

[Justin Hoffman]

"Neil" <ne**@logan-5.co.uk> wrote in message
news:dc**********@newsg1.svr.pol.co.uk...

I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.

I have had no end of problems and possible solutions to dates such as
1/8/05
being interpreted in the wrong way.

I have been given many suggestions (setting LCID, submitting dates in long
format etc. etc.)

Is there any definitive sloution or tutorial available? This is driving me
MAD!

I can get dates submitted OK but they get read back wrong. I alter the
code
and then they are submited wrong but read OK from the database.

Someone please help!!!!!!!!!!!!!!!!
Neil

ASP has pretty limited functions to format dates. There are other solutions
to this, but I would write my own function so I could be sure the dates were
displayed correctly regardless of any machine/software configuration. For
example:

Function UkDate(dteDate)

Dim strTemp
Dim strReturn

strTemp = Day(dteDate)
If Len(strTemp) = 1 Then strTemp = "0" & strTemp
strReturn = strTemp & "/"

strTemp = Month(dteDate)
If Len(strTemp) = 1 Then strTemp = "0" & strTemp
strReturn = strReturn & strTemp & "/"

strTemp = Year(dteDate)
strReturn = strReturn & strTemp & "/"

UkDate = strReturn

End Function
Then you could use this function wherever you liked in your ASP code. You
might add some error handling for when the variable passed is not a date or
is null.

[Neil]

Thanks for all your help guys. Much appreciated. I will give all those
suggestions a go.

Neil

"Justin Hoffman" <[e-mail]> wrote in message
news:dc**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

"Neil" <ne**@logan-5.co.uk> wrote in message
news:dc**********@newsg1.svr.pol.co.uk...

I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.

I have had no end of problems and possible solutions to dates such as
1/8/05
being interpreted in the wrong way.

I have been given many suggestions (setting LCID, submitting dates in
long
format etc. etc.)

Is there any definitive sloution or tutorial available? This is driving
me
MAD!

I can get dates submitted OK but they get read back wrong. I alter the
code
and then they are submited wrong but read OK from the database.

Someone please help!!!!!!!!!!!!!!!!
Neil

ASP has pretty limited functions to format dates. There are other
solutions to this, but I would write my own function so I could be sure
the dates were displayed correctly regardless of any machine/software
configuration. For example:

Function UkDate(dteDate)

Dim strTemp
Dim strReturn

strTemp = Day(dteDate)
If Len(strTemp) = 1 Then strTemp = "0" & strTemp
strReturn = strTemp & "/"

strTemp = Month(dteDate)
If Len(strTemp) = 1 Then strTemp = "0" & strTemp
strReturn = strReturn & strTemp & "/"

strTemp = Year(dteDate)
strReturn = strReturn & strTemp & "/"

UkDate = strReturn

End Function
Then you could use this function wherever you liked in your ASP code. You
might add some error handling for when the variable passed is not a date
or is null.

[N Benson]

Neil

Code:

SetLocale("en-gb")

At the beginning of your ASP pages and (hopefully!) all your problems will go
away..

Regards

Nigel

(In the Uk)

On Mon, 25 Jul 2005 22:56:56 +0100, "Neil" <ne**@logan-5.co.uk> wrote:

I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.

I have had no end of problems and possible solutions to dates such as 1/8/05
being interpreted in the wrong way.

I have been given many suggestions (setting LCID, submitting dates in long
format etc. etc.)

Is there any definitive sloution or tutorial available? This is driving me
MAD!

I can get dates submitted OK but they get read back wrong. I alter the code
and then they are submited wrong but read OK from the database.

Someone please help!!!!!!!!!!!!!!!!
Neil

[Neil]

Hi Nigel

I had already used

Session.LCID = 2057

at the beginning of my ASP pages.

Is that something different to what you suggested?

Thanks
Neil

"N Benson" <ni***@lbenny1.demon.co.uk> wrote in message
news:99********************************@4ax.com...

Neil

Code:

SetLocale("en-gb")

At the beginning of your ASP pages and (hopefully!) all your problems will
go
away..

Regards

Nigel

(In the Uk)

On Mon, 25 Jul 2005 22:56:56 +0100, "Neil" <ne**@logan-5.co.uk> wrote:

I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.

I have had no end of problems and possible solutions to dates such as
1/8/05
being interpreted in the wrong way.

I have been given many suggestions (setting LCID, submitting dates in long
format etc. etc.)

Is there any definitive sloution or tutorial available? This is driving me
MAD!

I can get dates submitted OK but they get read back wrong. I alter the
code
and then they are submited wrong but read OK from the database.

Someone please help!!!!!!!!!!!!!!!!
Neil

[N Benson]

Neil

I've never had a problem with Setlocale, however I think it only affects the
scripting engine that ASP uses. Session.LCID affects both the ASP application
and the scripting engine..

Another possible cause is the LCID number used, there are two for English - UK
2057 and 1033 - you need to have the language pack available on the machine. (Or
it'll probably revert to the default (Whatever that is!) Try using 1033 instead,
they may be different.

Regards

Nigel

On Tue, 26 Jul 2005 18:12:14 +0100, "Neil" <ne**@logan-5.co.uk> wrote:

Hi Nigel

I had already used

Session.LCID = 2057

at the beginning of my ASP pages.

Is that something different to what you suggested?

Thanks
Neil

"N Benson" <ni***@lbenny1.demon.co.uk> wrote in message
news:99********************************@4ax.com.. .

Neil

Code:

SetLocale("en-gb")

At the beginning of your ASP pages and (hopefully!) all your problems will
go
away..

Regards

Nigel

(In the Uk)

On Mon, 25 Jul 2005 22:56:56 +0100, "Neil" <ne**@logan-5.co.uk> wrote:

I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.

I have had no end of problems and possible solutions to dates such as
1/8/05
being interpreted in the wrong way.

I have been given many suggestions (setting LCID, submitting dates in long
format etc. etc.)

Is there any definitive sloution or tutorial available? This is driving me
MAD!

I can get dates submitted OK but they get read back wrong. I alter the
code
and then they are submited wrong but read OK from the database.

Someone please help!!!!!!!!!!!!!!!!
Neil

[Neil]

Thanks Nigel

I will investigate .....

"N Benson" <ni***@lbenny1.demon.co.uk> wrote in message
news:3k********************************@4ax.com...

Neil

I've never had a problem with Setlocale, however I think it only affects
the
scripting engine that ASP uses. Session.LCID affects both the ASP
application
and the scripting engine..

Another possible cause is the LCID number used, there are two for
English - UK
2057 and 1033 - you need to have the language pack available on the
machine. (Or
it'll probably revert to the default (Whatever that is!) Try using 1033
instead,
they may be different.

Regards

Nigel

On Tue, 26 Jul 2005 18:12:14 +0100, "Neil" <ne**@logan-5.co.uk> wrote:

Hi Nigel

I had already used

Session.LCID = 2057

at the beginning of my ASP pages.

Is that something different to what you suggested?

Thanks
Neil

"N Benson" <ni***@lbenny1.demon.co.uk> wrote in message
news:99********************************@4ax.com. ..

Neil

Code:

SetLocale("en-gb")

At the beginning of your ASP pages and (hopefully!) all your problems
will
go
away..

Regards

Nigel

(In the Uk)

On Mon, 25 Jul 2005 22:56:56 +0100, "Neil" <ne**@logan-5.co.uk> wrote:

I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.

I have had no end of problems and possible solutions to dates such as
1/8/05
being interpreted in the wrong way.

I have been given many suggestions (setting LCID, submitting dates in
long
format etc. etc.)

Is there any definitive sloution or tutorial available? This is driving
me
MAD!

I can get dates submitted OK but they get read back wrong. I alter the
code
and then they are submited wrong but read OK from the database.

Someone please help!!!!!!!!!!!!!!!!
Neil