[RESOLVED] Old Access question... Date Input Masks...

[gigemboy]

Trying to set up the user to be able to input a date in the format of DD-MM-YYYY instead of the month first, but I do not see how to go about doing that. Is this possible in Access? I have read posts on how input masks are not a good way to go about it, to just set it up as a Short Date and let the user enter in the values as he pleases, but how about when the date for January 3rd is entered in as 03-01-2008? How does access know that we are meaning January 3rd and not March 1st? How can we tell access that the first value is the day value and not the month value when entering data?

[Hack]

but how about when the date for January 3rd is entered in as 03-01-2008? How does access know that we are meaning January 3rd and not March 1st?

Because of your regional settings dealing with dates.

[si_the_geek]

If you use the 'Short Date' mask, it will be as the user expects it to be - because it will work the same way as the rest of their computer (as it uses Regional Settings). If they normally use "dd-mm-yyyy", when they enter "03-01-2008" it will be taken to mean January 3rd.

Any data that is already in the table will be displayed in their format. Note that a Date does not get stored in any format at all, it just gets stored as a value (exactly how varies by database/language, but typically as a number of days since a certain point in time).

If you are using forms, you could use a proper Date based control, as explained in the FAQ article What controls can I use to input a date/time?. As long as you take the Date value (rather than a String) and work with that, there are no issues whatsoever - as problems occur when converting a String to a Date (or vice-versa in some situations).

[gigemboy]

Well I understand that it is displayed according to their regional settings, but I was asked to try and change the way the date is entered (different way than our regional settings), with the day being entered in first rather than the month according to a particular form the users are using to record the data out in the field. When they return from the field to record the data in the database, they would like to have the access form set up the same way their field paper forms are, which is day first. I thought there would be an easy way to do this, but it is looking like there isn't. I dont want to have the user to pick a date from a calendar control, or any other form of control that would take extra time to enter in hundreds of forms at a time. I just wanted a simple textbox to speed the data entry...

[si_the_geek]

In that case, use a textbox (or masked input of some sort) with a caption that mentions the format.

Then either store it to a Text based field (which will cause problems for queries), or convert it into a Date value (sample code at the end of this article) and store it in a Date based field.

[VBFnewcomer]

why worry about Calendar control. There is DateTime Picker which acts just like a text box. If you set is format to dtpCustom and specifiy a format it'll be brezee for the users. Any way its only the display access I believe stores date in mm/dd/yy format

[si_the_geek]

access I believe stores date in mm/dd/yy format

No it doesn't:

Note that a Date does not get stored in any format at all, it just gets stored as a value (exactly how varies by database/language, but typically as a number of days since a certain point in time).

Just like in VB, for Access a Date actually gets stored as a Double, with the integer part being a number of days since a fixed date (can't remember what), and the fractional part being the time since midnight (so .75 is 6PM).

Formats only ever come in to play for Strings - when you are converting a Date to a String (eg: displaying it in a TextBox, or in the Watch window), or when converting from a String to a Date (such as putting a TextBox value into a Date variable, or as a parameter to functions like DateAdd/DateDiff).

A String to Date conversion is likely to go wrong if you don't write code which takes account of the format the String is in.

[gigemboy]

The solution actually was to use a datetimepicker control (I believe the one I was using was a Microsoft DTPicker ActiveX object??). There is a CustomFormat property that you can set to display/enter the date using the format that you use. This allowed them to either type in the date in a combobox or drop down to pick the date.