A2K Date format issues frustration, need help.... 1

[YACHTIE]

I am rather frustrated with some date issues I have encountered lately, I inherited these 2 databases that have several date fields, all specifying "Medium date" with some also having the input mask as "99\->L<LL\-00;0;" and some as "99\->L<LL\-00;0;_" and some others with no input mask.

First question: What is the significance of the "_" suffix on the mask.
Second question: Can Anyone think of a reason why the input masks is only applied several places and not for all date fields?
Third question: Why are dates mixed in various places with yr-mmm-dd and dd-mmm-yr, I am pretty sure the original inputs were done yr-mm-dd.
Fourth question: Why does the date "yr-mm-dd" change to "dd-mmm=yr" when clicked on a date in the tables, there was a thread I saw on this problem in the archives but no solution or fix was given, anyone know why that is?

My suspicion on the 3rd question is that with many people having had write access recently I believe that some PC's default settings (Regional Language Options) were day-month-yr, which may have caused dates to change. Could that be the reason?

Any way to fix this and make all date entries consistent?

Look forward to hearing your opinions.

Thanks

 

[Crowley16]

1) check in the help for a description of all input mask characters

2) input masks force people to enter information according to a format, if it's not important for them to do this, then there's no need to put it in, since by formatting the field as date, the control will automatically error on non-date entries...

3) & 4) Access is kinda funny about date formats. Basically, it will look at the date format specified in the o/s regional settings to display dates, however the fields will have to be formatted as date for this to happen. Also, access sql tends to use american date format if you put in the date delimiters ##.
Generally, be very careful about dates, either make sure that they are set as the date datatype throughout or put in code to handle possible o/s setting differences, preferably both...

--------------------
Procrastinate Now!

 

[YACHTIE]

Thanks for the comments, however I had previously checked help on the input masks and the underscore is not shown/mentioned there.

 

[JoeAtWork]

As for questions # 2 and 3, the answer is probably that previous programmer(s) did not bother to try to be consistent in the presentation and format of dates. Your best option would probably be to decide for yourself what the standard format for dates should be, then enforce across all forms and reports.

 

[Crowley16]

direct from F1

Microsoft Access interprets characters in the InputMask property definition as shown in the following table. To define a literal character, enter any character other than those shown in the table, including spaces and symbols. To define one of the following characters as a literal character, precede that character with a backslash (\).



--------------------
Procrastinate Now!

 

[lespaul]

Additionally a database stores a date field as an integer in the number of days since 12/30/1899 and the time in the decimal portion of the number.
Today is: 39171; 9:21 in the morning is .3901709375

the function Now() on 3/30/2007 at 9:21 would return:
39171.3901709375

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[YACHTIE]

thanks for the response,I'd still like some clarification though, What does the underscore do for me and secondly should I make all input masks contains that underscore or remove it?
IOW what does the underscore really do for me if anything?

Furthermore, Crowley16, this is what my F1 produces fro the input mask question, and i do not see anything there for the underscore.

"When you create an input mask, you can use special characters to require that certain data be entered (for example, the area code for a phone number) and that other data be optional (such as a telephone extension). These characters specify the type of data, such as a number or character, that you must enter for each character in the input mask.

You can define an input mask by using the following characters.

Character Description
0 Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).
9 Digit or space (entry not required, plus and minus signs not allowed).
# Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).
L Letter (A to Z, entry required).
? Letter (A to Z, entry optional).
A Letter or digit (entry required).
a Letter or digit (entry optional).
& Any character or a space (entry required).
C Any character or a space (entry optional).
. , : ; - / Decimal placeholder and thousand, date, and time separators. (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel).
< Causes all characters to be converted to lowercase.
> Causes all characters to be converted to uppercase.
! Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.
\ Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A)."

 

[PHV]

The underscore has no special meaning, but the important thing is that it is in the THIRD section of the mask ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886