How to input date in a custom format?

tooold

New member
Local time
Yesterday, 18:17
Joined
Jan 26, 2008
Messages
6
Hi, I'm having a problem like this:
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 [orderdate] 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. (it's going out OK when the date is 13/1/2007) :confused:

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.
 
Unless you use the Sister Mary Margaret Method (look over your user's shoulder with a steel ruler in hand to rap knuckles with) I don't know anyway to force a user to enter a date in a particular format! The problem, as you pointed out, is that validation can't be done because 01/03/2007 and 03/01/2007 are both valid date values! How can Access tell whether exactly what the user means?

What I would do is let the user enter the date in their native way, which is to say in the manner their Regional Settings dictate, then format it in the British manner in the AfterUpdate event of the control.
Code:
Private Sub OrderDate_AfterUpdate()
  Me.OrderDate = Format(Me.OrderDate, "dd/mm/yyyy")
End Sub

If an American user enters 3/1/2007 for March 1, 2007, this will yield 01/03/2007.

If a British user enters 1/3/2007 for March 1, 2007, it will also yield 01/03/2007!

Hope this helps!

Linq
 
Thanks for the tip, but what I want to do is to make the format correct right when the user get to the field.
I already made is like this:

Private Sub OrderDate_Enter()
Me.OderDate = Format(Me.OderDate, "dd/mm/yyyy")
End Sub

Questions:
1) Is this possible?
2) If yes, what's wrong with my coding? (I got an error message from Access)
 
Anyway, I still got an error message from Access even when i Put the code like this:
Private Sub OrderDate_AfterUpdate()
Me.OrderDate = Format(Me.OrderDate, "dd/mm/yyyy")
End Sub
What's wrong then?????
 
There is no "making the format correct" when entering the field. You cannot force the user to enter the data correctly in this manner! How would Access know which was correct, 1/3/2007 or 3/1/2007? They're both valid dates!

Where exactly did you place the code? It needs to be in the code window (VBA Code Editor) not in the Property Box.

What error message are you getting?

You also need to double check your code for correct names. For instance in this code

Private Sub OrderDate_Enter()
Me.OderDate = Format(Me.OderDate, "dd/mm/yyyy")
End Sub

your control name appears to be spelled incorrectly.
 
Hi,
I've got this kind of error (see the pictured enclosed). Can you tell me me more what's wrong?
(I've already checked all the possible spelling errors and put the code in the code window :confused:
Thanks
 

Attachments

  • error.jpg
    error.jpg
    32.5 KB · Views: 173
You've identified difficulties associated with amiguities in text representation of dates.

Two ways to get round this are:

1) Use three Combo boxes (drop down lists) , day month and year.

2) Use a calendar style date picker control.

Any help with these two approaches needed I'm sure you can post here.
 

Attachments

  • datepicker.GIF
    datepicker.GIF
    36.5 KB · Views: 147
what difference how they input it?

its still stored as the same date - surely the issue is how you use it and manage it subsequently
 
what difference how they input it?

its still stored as the same date - surely the issue is how you use it and manage it subsequently

The issue is that dates input as text are potentially ambiguous, i.e. 1/7/2009 can be either Jan 7 or July 1.

No such amibguity exists with the date picker or drop down lists.
 

Users who are viewing this thread

Back
Top Bottom