How to handle date without alter the sytem date format 1

[sansangtracao]

I have a date field which I want to put Date in with other format (dd/mm/yyyy) and I dont want to alter the system date (mm/dd/yyyy). I already put in the property sheet of this field the new format (dd/mm/yyyy). What happened so far is when i put in 01/03/2008 (January 3, 2008) it always comes out with 03/01/2008 (March 01, 2008), every thing is just find with other date (greater than 12).
Any help of how to overcome this is highly appreciated.

 

[skyline666]

Hi,

Heres a few links to sites which show how to change the date format.

This one is pure changing date formats:

http://www.sql-server-helper.com/tips/date-formats.aspx

This one shows how to take just the date from a date/time function for SQL Server (which is irrelevant to you but could be useful in the future):

http://blog.sqlauthority.com/2007/0...t-only-date-part-from-datetime-best-practice/

This one shows how to add/subtract days/months/years to the date you specify (again irrelevant but maybe useful):

http://www.sqlhacks.com/index.php/Dates/CalculateDates

Hope this helps,

Andrew

 

[sansangtracao]

Thanks for the tip, however it still does not answer my question, and my question is just what do i put in the 'format' field of the properties of the field so user can put in a date with dd/mm/yyyy format.
Thanks

 

[JoeAtWork]

The format property is for display, it does not enforce how what is typed in is interpreted. To override how the date is interpreted, I think you would have to write code to rearrange the characters.

However, why not just let the user enter the date in the format they prefer? It's standard windows programming to respect the date/time preferences the user has set.

What I do is set the display format so that the user can see how the system is going to interpret the date, for example dd-mmm-yyyy. So if they enter 1/3/2007 as soon as they tab to the next control they will either see "3-Jan-2007" or "1-Mar-2007" and will know how the system is interpreting the date.

 

[skyline666]

Talking of interpreting dates as JoeAtWork said, I did find this site which doesn't give an actual answer, but explains more on what JoeAtWork said:

http://allenbrowne.com/ser-36.html

Also, that link was found on the below link, which could come in handy if you decide to format the date through code (which you might have to do if putting dd/mm/yyyy doesnt work on the format properties):

http://www.access-programmers.co.uk/forums/showthread.php?t=74418

 

[sansangtracao]

Hi, thanks for the helps. Maybe I still have not put the question in a clearer way, here is the problem:
I want users to put date in the field [orderdate] in the UK format (dd/mm/yyyy) no matter what their system date would be.
I already put in the fomat property of the field the desired format (dd/mm/yyyy). However, it somehow does not come out properly: when I have a date like 01 March 2007 which I put in 01/03/2007 it will automatically come out with 03/01/2007 meaning 03 January 2007.
Quetions:
1- How do I fix this problem?
2- Is there anyway to put make sure that users input the date in a correct format?
Thanks.

 

[MajP]

I want users to put date in the field [orderdate] in the UK format (dd/mm/yyyy) no matter what their system date would be.

Why do you want to ensure they enter it in a certain format?

Just to reiterate any date format is saved the same way, as an integer. For example today is 39472 and can be shown in any format(39472 days since Dec 31 1890)

?clng(date)
39472

?format(cdate(39472),"mm/dd/yyyy")
01/25/2008

?format(cdate(39472),"mm/dd/yy")
01/25/08

?format(cdate(39472),"dd/mm/yy")
25/01/08

?format(cdate(39472),"dd mmm yyyy")
25 Jan 2008

?format(cdate(39472),"mmmm, dd, yyyy")
January, 25, 2008

etc.

So if I am in the US and have US date time settings, and I enter
03/04/2007 you want to ensure that I meant April 3 not March 4th. Seems confusing. I would look at JoeAtWorks post to allow them to view the format in a 3-Jan-2007" or "1-Mar-2007

But if you want to force an accurate date use something besides a textbox for entry: Calendar control, date time picker, or three combo boxes (day,month,year).

 

[sansangtracao]

Thanks MajP, can you tell me more about the solution? where do i put this "?format(cdate(39472),"dd/mm/yy")" expression?

 

[JoeAtWork]

I think you should consider more carefully what myself, MajP, and others are trying to explain. Forcing the users to type in a certain format is not the way to ensure accuracy. What it comes down to is you will never know for sure if a user types in 01/03/2008 whether they really meant January 3rd or March 1st. Presumably the user does know what their local settings are, and if your application is the single one on the computer that does the opposite of all the other applications (because yours doesn't respect the regional settings) you are in fact inviting mistakes to happen.

Format for dates is only important for display. No one can answer your question, as it is impossible to program the computer to read the user's mind to find out if they really meant January 3rd or March 1st.

Instead, you should try to make an interface where it is unambigious as to what they should type in. Besides a calendar control, you could put three dropdowns, one having 1 to 12 (or Jan to Dec), another having 1 to 31, and the third having a range of years (the exact range depending on the context).

 

[sansangtracao]

Yeah, now I can understand what you guys are trying to explain to me. But what I'm trying to do is this: an user can put in the date as dd/mm/yyyy (1 March, 2007) without MS Access automatically reverse the date back to mm/dd/yyyy (3 January 2007)!
And I'm not sure why it always reverses the dates, which are less that 12 to the original order which is mm/dd/yyyy when it does not effect the dates that are greater than 12.
Thanks

 

[sansangtracao]

I tried this code on the AfterUpdate event:
Private Sub OrderDate_AfterUpdate()
Me.OrderDate = Format(Me.OrderDate, "dd/mm/yyyy")
End Sub
This still does not work. Please tell me what's wrong with the code? Thanks

 

[MajP]

Did you read skyline666 links? Allen Browne discusses this issue pretty clearly:

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 !!!

There is nothing wrong with the code, but Bill Gates is thinking for you.
I would use a control other than a text box to ensure the user inputs 3 April vice 4 March.

 

[SkipVought]

Bottom line:

Use an UNAMBIGUOUS entry format: yyyy/mm/dd in order to be ABSOLUTELY certain of the date that you are entering.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[sansangtracao]

Thank you very much. Here is my solution for now: let it be in the format like this: dd/MM/yyyy on form, but on the report it will be dd/mm/yyyy (since all my employers can somehow understand English) Damn Microsoft anyway!

 

[skyline666]

Heres something else you could do aswell. As JoeAtWork said:

No one can answer your question, as it is impossible to program the computer to read the user's mind to find out if they really meant January 3rd or March 1st.

then what you could do is have a label next to the box where the user enters the date (assuming you are having the box still and not the 3 drop down boxes as suggested previously) which simply says dd/mm/yyyy. This then tells the user to enter the date in that format, not mm/dd/yyyy for example.

This doesn't solve the issue of Access swapping round the date, but it does sort out the issue of the user entering it right, as long as they read it!

Andrew

 

[JoeAtWork]

The rule of thumb for designing a user interface is to make it as easy as possible for the user not to make a mistake.
With that in mind, I list the various approaches, ranked in order of the most unambigious (and therefore least prone to error).
1. Calendar control
2. Dropdowns for month, day, and year
3. Textbox labelled with expected format
4. Plain textbox

Quite frankly, I can't conceive why you wouldn't use the calendar control 100% of the time. I can't see it as being a space issue, since we almost always implement these as dropdowns that disappear once the entry is made. Even if this is not possible, you could put the calendar control in dialog box.