Jump to content

Another ASP/Access Date Issue


Holdstrong

Recommended Posts

Hi everyone! Loooooong time browser, first time problem poster!I have an ASP page that has been active and functional for almost 4 years. It is hosted by a third party company and just recently they brought us down to do some hardware upgrades.Upon coming back up I noticed a rather odd problem...The dates that my page is capturing from a user form are being stored incorrectly in our Access Db. 2/7/2007 will be stored as 7/2/2007 and vice versa.However, 2/13/2007 will be correctly stored as 2/13/2007. I have since learned that what is most likely going on here is that Access is suddenly (because of the upgrades?) storing dates in the UK standard dd/mm/yyyy instead of the US standard mm/dd/yyyy. The reason it correctly stores dates with "days" 13 and above is because it recognizes that 13 can not be a month and assumes user error and corrects it.So here is my problem. I need these dates to be stored as they are being passed... mm/dd/yyyy (I have confirmed they are being passed correctly still by sending them to a test TEXT field in which they arrive unaltered). Is there anyway, short of what I am sure will be the fruitless pursuit of asking my host to fix the problem on their end, to FORCE Access to just accept the darn date the way it is receiving it?

Link to comment
Share on other sites

I found this gem of truth and justice:

Define the date format for your locale in Windows Control Panel | Regional Settings. You can therefore use your local date format when you enter dates into the user interface part of Access: tables, queries, forms, or the Criteria of Query Design View.Unfortunately, Microsoft tried to be too smart at helping Access accept dates. If you enter a date that is invalid for your local settings, Access spins the date around trying to find an interpretation that works. For example, with British dates in Control Panel, if you enter 10/13/01, Access realises there is no 13th month, and decides you must have intended 13-Oct-01. The results can be bizarre. The entry 02/29/01 should generate an error message that 2001 is not a leap year. It doesn't. Instead, Access plays with the entry and decides you must have intended Feb-1-2029 !!!Aside from this madness (which cannot be turned off), just remember the user interface in Access uses the local Control Panel settings to interpret dates typed into the user interface.
http://allenbrowne.com/ser-36.htmlDespite what that says, you may be able to find an answer here:http://www.dmxzone.com/ShowDetail.asp?NewsId=4828Or, if all else fails:http://www.google.com/search?hl=en&cli...amp;btnG=Search
Link to comment
Share on other sites

Thanks for the reply and great info!So, since my first post I have done the following test... If I insert an unambiguous date, such as 7-feb-2007 (dd/mmm/yyyy) or 2007/2/7 (yyyy/mm/dd) Access does receive and store the info correctly.Seems pretty clear we are dealing with a regional settings info somewhere between the ASP page and that Access Db. However, this site is hosted by a third party and I have no access to these machines. I am in contact with them about the problem and they are looking into it, but I am not holding my breath that they will either find the problem, or correct it.However, it looks like one option would be to force people to insert the date in an unambiguous format... but, as we all know, relying on your users to do something, especially something which will feel foreign to them, is unlikely to produce happy results. However, what if there was a way for me to take the values they enter in those forms and force a format change behind the scenes BEFORE it gets passed to Access?In other words, my user enters 2/7/2007 in my form. That value is saved as "mydate1" via simple form HTML:

<input type=text name="mydate1" size=12 class="9pxTextCopy">

and then is passed along to he database as is via:

objrs.fields("mydate1") = request.form("mydate1")

But what if before passing along to the database I converted "mydate1" somehow... maybe using FormatDateTime()??Here is where my extreme noobness will shine. I have no idea how I would take a value from a Form ("mydate1") and run it through something like FormateDateTime before sending it off to the Db.

Link to comment
Share on other sites

Most of the time when I'm dealing with entering dates like this, it's easiest to just have one field for month, another for day, and another for year. That way you can format it however you want and there's no confusion for the user. And you can also check for errors, if the month field contains 13 then there is an error, there's no question about if they meant 13 as the day or not.Don't hold your breath on the server config. It looks like a global control panel setting for the entire server, so you can thank Microsoft for that design decision.

Link to comment
Share on other sites

Most of the time when I'm dealing with entering dates like this, it's easiest to just have one field for month, another for day, and another for year. That way you can format it however you want and there's no confusion for the user. And you can also check for errors, if the month field contains 13 then there is an error, there's no question about if they meant 13 as the day or not.Don't hold your breath on the server config. It looks like a global control panel setting for the entire server, so you can thank Microsoft for that design decision.
Yeah that would certainly be a solution, unfortunately that would require some serious rewrites of quite a few pages... and given my abilities (or lack there of) I am going to save that for dead last.If there was some way I could run a conversion on the form input before sending it off to the database that would seem to solve the issue. Just unsure of how to do it. Possibly by linking a function to the OnSubmit part of the form so that when the user clicks submit a function runs that converts "mydate1" and "enddate" to unambiguous formats first and then returns true to send off to the db?
Link to comment
Share on other sites

That's the trick though, a text date is not unambiguous. Converting it would basically do the same thing that Access is doing. The only truly unambiguous way is to have 3 separate text fields for the date. You could put a note along these lines:Enter Date (mm/dd/yyyy)And then just split the date up into the three parts yourself, and then reformat it however it needs to be formatted. That could certainly be done, but it's not 100% unambiguous because it still relies on the user doing the right thing. And experience has shown that a user will only do the right thing if you force them to.

Link to comment
Share on other sites

That's the trick though, a text date is not unambiguous. Converting it would basically do the same thing that Access is doing. The only truly unambiguous way is to have 3 separate text fields for the date. You could put a note along these lines:Enter Date (mm/dd/yyyy)And then just split the date up into the three parts yourself, and then reformat it however it needs to be formatted. That could certainly be done, but it's not 100% unambiguous because it still relies on the user doing the right thing. And experience has shown that a user will only do the right thing if you force them to.
Thanks for the continued input, I really do appreciate it.Now, correct my thinking again if it is wrong... but I just made the following test page on my server:Date Test PageThat page is a close mirror to the actual page I am working on. It is modified so that when you enter a date in US format, or in an unambiguous format such as yyyy/mm/dd, it will attempt to convert it to the unambiguous format d/mmm/yyyy and display it at the top of the page.Seems to work. Now doesn't this show that the page itself is actually translated dates correctly? Or at the very least, doesn't it show that we can convert user entered strings to an unambiguous format?If the latter is true, then shouldn't it just be a case of passing the converted date to the database instead of the raw string originally entered by the user?
Link to comment
Share on other sites

Yeah, you can certainly convert the dates to something unambiguous, and give that to the database. But you are still counting on the user entering the data correctly. If someone types in 3/7/07 and they meant July 3, there's no way for you to know that.

Link to comment
Share on other sites

Yeah, you can certainly convert the dates to something unambiguous, and give that to the database. But you are still counting on the user entering the data correctly. If someone types in 3/7/07 and they meant July 3, there's no way for you to know that.
Great point. Thanks for talking me through this. I certainly have a much better handle of the problem now. Luckily for me, my user base is a know entity, and how they will enter the dates is also known. They will use US Standard. So this cuts me a break, so to speak. Instead of completely re-doing every page that asks them for a date so that they enter months, days, and years into separate boxes... I can just convert their entries to an unambiguous format and then send that along to the Db.Here was my solution.I run the following code at the top of my page:
correctDate = day(request("mydate1")) & "-" & monthName(month(request("mydate1"))) & "-" & year(request("mydate1"))correctEnddate = day(request("enddate")) & "-" & monthName(month(request("enddate"))) & "-" & year(request("enddate"))

And then, change this:

objrs.fields("date") = request.form("mydate1")objrs.fields("end_date") = request.form("enddate")

To this:

objrs.fields("date") = correctDateobjrs.fields("end_date") = correctEnddate

And bam, I am not converting the US Standard mm/dd/yyyy that my users enter to the semi-unambiguous dd/mmm/yyyy and sending THAT along to my Db instead. The Db is accepting it correctly and stores and then displays it correctly.

Link to comment
Share on other sites

Put Session.LCID = 1033 at the top of the header page.this will make all dates into the correct format for you.'----1033 = USA2057 = British
That is a neat piece of code and I am glad you mention it. I learned about it while researching this problem and I have tucked it away for future use/reference. However, since the problem was not in the displaying of dates on the actual site, but was instead in the storing of dates in the Access database, it didn't help me with this particular problem.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...