Access uk american date format problem

Date/Time values in Access are stored as a floating point datatype giving the number of days since 31st Dec 1899 - the decimal portion of the number represents the time as a fraction of a day.
The formatted version of the date will show according to the Windows international settings on the PC. Thus you can tell that I'm in the UK:
?now, cdbl(now)
29/09/2007 13:38:05          39354.5681134259
?cdate(39354.5)
29/09/2007 12:00:00

When you insert a date as a number Access will interpret it according to the international settings unless the day is >12 when the intention is unambiguous.

Thus it's always safer to use the word form of the month.

Hope this helps

Thanks Mike
I am not sure I entirely followed your thoughts but I changed the date format in access to long date and entered 01-october-2007 into the form on  the asp page but the date still appears as 10 January 2007.

It seems the format gets lost in the transfer from the asp page to access.  As I said if 01 oct 2007 is entered direct into access it stays correct

I forgot I checked my regional settings and they are set for the uk

Using sql to insert the date into access is always taken as American format, the easiest solutions is to use the format yyyy/mm/dd when inserting dates.

Thanks Gary
I entered 2007/oct/01 in the form and in acess it shows as 10 January 2007

That should work fine, just double checked it myself, in design view when you click format do the example dates show in uk format?

Just double checking you meant access design view ?
In format I have Long Date (now) and that is showing as 19 June 1944

To recap:
I enter 01-oct-2007 then 10 Jan 2007 shows in the table
I enter 10/01/2007 then   01 Oct 2007 shows in the table
I entered 10-Jan-2007 then 01-Oct-07 shows in the table
Woe is me.....

Do you mean entering those dates directly into Access
Do you have any kind of masks set up on the column?

Also are you sure you have uk date/time format set in your regional settings

Thanks
No,  if I enter them via the form in the asp page

If I enter directly into the table its correct

There are no masks

Ok, but you need to enter in the format yyyy-mm-dd,
Access always takes the dates in American format unless you have a date like 13 Jan 2007 which Access realises isnt American format and so interprets it as 13 Jan 2007, whereas 12 Jan 2007 would be seen by Access as Dec 01 2007.

"Ok, but you need to enter in the format yyyy-mm-dd,"

I entered 2007-oct-01 in the form and in the table I see 10-Jan-07
I think thats what you suggested

I tried a completely new page and form to insert from with just one field, same result.
I have other websites like this which work pefectly.
I have also carried out a compact and repair on  the database
I tried changing regional settings to english US

Does one get these problems in sql or oracle or mysql do you think?

John,
<I am not sure I entirely followed your thoughts but I changed the date format in access to long date >
What I was trying to say was that Access stores a dates without any formatting - date formats are used only for the display of dates and the interpretation of dd/mm versus mm/dd when entered through the user interface.

<I entered 10-Jan-2007 then 01-Oct-07 shows in the table >
If you pass the string 10-Jan to Access, it will get it right whatever the international settings on the PC. I suspct therefore that a date conversion is happening in the web-side of the application, before the date is passed to Access.

How is the textbox on your DreamWeaver form hooked into Access? Is there any code on the Web page?

And, yes - you have to be careful about dates in Oracle and SQL Server too.

Thanks yes it can only be the code on the web page now.
I will have a look into the page code.
I am now using Adobe cs3 whereas previously I used DW mx 2004. Nothing else has changed as far as I know.
So I think I will create a new page using DreamweaverMX 2004
CS3 has one bug built in as I discovered via Jason of dreamweaver Fame,  that is the repeat region has to be inserted via the code view whereas it used to be inserted OK in design view. Such are the improvements!

I opened the site in DWMX2004 and created a test page to insert a date into access and it worked perfectly.
Unfortunately it seems that CS3 is not backwards compatible. Open a page created in CS3 with a recordset from DW MX 2004 and  the recordsets have disaapeared.
I read the problem with access is something to do with using a DSN less connection. I am using oledb and dont wish to go back to DSN

Are you assigning the date in code? If so can you post your code? Maybe we can work something out.

"Are you assigning the date in code?"
Very sorry Mike would you explain this a bit further?
Thanks

<"Are you assigning the date in code?">
This goes back to theearlier point:
<How is the textbox on your DreamWeaver form hooked into Access? Is there any code on the Web page?>

It seems that is you enter 01-oct-2007 directly into Acces, it's OK, but if you do it via your web form's textbox you get  10 Jan 2007. That being so, the problem must be in the means by which the data in the text box gets transferred to Access.

One thought: Since the move of data from the textbox to Access happens during the processing of the Web Form, the Windows International  Settings that matter are those on the Web Server, not those on the client PC. The Web form will just pass whatever's entered in the text box as a string to the server, so the date's OK when it gets there.

Thanks Mike
Regarding the regional settings, I am using local host  on this pc for testing etc and unless I am wrong I believe the local host is also controlled by the PC regional settings. The Regional settings are set to English United Kingdom.
Next week I am going to TRY to get some info from Adobe direct on the subject of CS3 and DSN less connections. After all its pretty important to all users.
I will come back when I have more news.
Regards John

Just to keep you informed I have been in toiuch with the technical department at Adobe and they state they will come back to me with further information.

Just to update,
Although I am in touch with the adobe technicals by email I dont seem to have made much progress. I expect I will have to do some chasing.

Nothing further from Adobe yet however
I created a very simple
Access database and insert form in Dreamweaver CS3
I then entered a number of dates via the form and below is  the result.
The DateDate Field is a medium date format
The WrittenDate is the entered date copies and pasted into Written date for comparison
It seems that day 12 and below are entered incorrectly
whereas 13 and above are entered correctly
DateID      DateDate      WrittenDate
14      01-Jan-08      01/01/2008
17      01-Feb-08      02/01/2008
18      01-Mar-08      03/01/2008
19      01-Dec-08      12/01/2008
22      13-Jan-08      13/01/2008
23      14-Jan-08      14/01/2008
24      13-Jan-07      13/01/2007
25      01-Dec-08      12/01/2008

I also ran a similar insert routine in DW MX2004 and found no problem

I have had no further help from adobe in spite of sending two futher requests.
As you will see from the above
entering dates into access from CS3 does not work for me but
entering dates into access from DW MX2004 works fine.
Any ideas on this please?
Thanks John

I eventually found an answer to this problem and thought that others may well be interested.
Anyone using UK date formats in Dreamweaver CS3 and access may be interested
http://www.dmxzone.com/forum/post.asp?method=Reply&topic_id=35929&forum_id=7&cat_id=5
I hope it helps someone.

Ouch, that looks ugly!
The only other thought I had was to try to assign the actual value that will be stored in the Access database. The following is a snippet from the immediate window in Access:

?date, clng(date)
23/10/2007     39378

The significance of this is that the value 39378 is stored in Access no matter what the displayed format of the date is. If Dreamweaver will accept 39378 and pass it through to Access then it will solve the problem.

Sorry MikeToole I dd not see this come in.
Would you please explain what to do with the above code
Many thanks

John,

No matter how you throw a data at Access, it stores it internally as a floating point value containing the number of days since Dec 31st 1899, plus a fraction of a day to represent the time. My example shows that the 23rd Oct 2007 00:00 is stored as 39378. Twelve noon on the same day would be stored as 39378.5

Access translates dates to this internal format when you assign them, but it also allows you to assign the raw number to the date field. As an alternative to the kludge suggested, I was wondering whether you could convert your date to the Access internal value and assign that in your dreamweaver code. It's probably a non-starter, but I just thought that I'd mention it.

Cheers,
Mike

Thanks Mike
"convert your date to the Access internal value and assign that in your dreamweaver code"
It sounds a great idea but I have no idea how that could be achieved

If you can code in VB Script there are two functions that return a date variable:
DateSerial(year, month, day), e.g. DateValue(2006, 12, 13)
DateValue(Datestring) , e.g. DateValue("December 13, 2006")
You could then use either of these in conjunction with the CDbl() function to return the date as a number.
The following example from the Immediate code execution window in a VBA environment illustrates what I mean.  The ? prints the results of the two expressions called in the line. For the first, DateSerial() returns a date variable that vb then converts to my default date format from my PC's International settings. In the second expression, theCDbl() function converts the Date variable to a number before it is printed. It is this number that is stored by Access.
?DateSerial(2006,12,13), CDbl(DateSerial(2006,12,13))
13/12/2006     39064
 
It's a long shot, but I thought it worth mentioning.
Mike

Closed, 500 points refunded.
Computer101
EE Admin