[AccessD] Format wierdness - was - TransferText wierdness

Thank you A.D.

As ever, you provide a very clear explanation that is the definitive solution to the puzzle. It was indeed a misinterpreted Date value problem after all. And to think that I doubted Gustav's word.

A.D., your skills of analysis and coding never cease to amaze me.

Thank you again,

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Saturday, July 18, 2009 9:39 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Format wierdness - was - TransferText wierdness

Lambert,

    You have touched upon an interesting aspect of format function. My findings (Access 2003 on Win XP) are placed below, for corroborative verification at your end as well as by other interested members of this forum.

    The result "-000-50-3778" given by Format("520-09-0012","000-00-0000") is explained by the fact that "520-09-0012" gets interpreted as 12-Sep-520, which has an offset of  -503778 days as compared to 30-Dec-1899 (reckoned as the zero point). This value when accommodated in format string "000-00-0000", right to left, gives "-000-50-3778"

    In the above input string, if the last component is given a value greater than "0030" (as there are only 30 days in Sep) or less than "0001", e.g. "520-09-0031" or "520-09-0000", it is no longer interpretable as a date and there is no aberration in the result.

    However, it is to be noted that the seemingly satisfactory result "520-09-0031" given by the expression Format("520-09-0031","000-00-0000") does not represent successful enforcement of Format() function. Rather, it is an outcome of the fact that formatting has not been performed at all and the original string stands unscathed. This is because format string with zero's as place holders becomes effective only in following cases:

    (a) The input argument is either a number or a string consisting purely of digits, satisfying the test  IsNumeric("<<InputString>>") = True.
         For example, Format("11", "000-00-0000") will give "000-00-0011", while  Format("'11", "000-00-0000"), where a single quote has been placed at the beginning of input string, will just give "'11" (i.e. the original string).

    (b) The input argument is a string that despite not being made up of pure digits, can be interpreted as date, satisfying the test IsDate("<<InputString>>") = True.
         In such a case the days offset with respect to 30-Dec-1899 is subjected to the intended format action, filling up the place holders from right to left (default behavior). Access is very pro-active in interpreting strings as dates and will precipitate this formatting behavior whenever there is the slightest scope for doing so. It is a different matter that the resulting formatted string does not make any apparent sense and looks weird.

    If it is desired that irrespective of the character combinations, the input string should invariably be treated as string and formatted as desired, @ has to be used as place holder in lieu of zero. In this case, formatting will always be successful, without any weird results, even when non-digital characters are present.

    However, for non-distorted results, an important over-riding precaution as stated below, needs to be exercised in both cases (whether @ or 0 is used as place holder):

    "None Of The Characters Used As Separators In Formatting String Should Be Present In The Input String."

Best wishes,
A.D. Tejpal
------------