A.D.Tejpal
adtp at airtelmail.in
Sat Jul 18 08:38:57 CDT 2009
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
------------
----- Original Message -----
From: Gustav Brock
To: accessd at databaseadvisors.com
Sent: Friday, July 17, 2009 20:06
Subject: Re: [AccessD] Format wierdness - was - TransferText wierdness
Hi Lambert
So some of those 94 strings could not be read as a date? Could you provide an example please?
You can easily evaluate the 94 values for date similarities with IsDate:
? IsDate("123-45-6789")
False
? IsDate("123-12-0029")
True
/gustav
>>> Lambert.Heenan at aiuholdings.com 17-07-2009 16:19 >>>
Gustav,
I don't buy the date theory, for the simple reason that these peculiar results only happened for 94 out of 15,000 records. Something else is going on, but I'm damned if I can figure out what!
One thing's for sure, it is not an issue with what data is actually stored in the table fields, because the "wrong" results happen even when passing in literal text values rather than field values from the table.
Lambert
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, July 17, 2009 10:03 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Format wierdness - was - TransferText wierdness
Hi Lambert
Ha! Seems like I missed it too. Stuart's and Asger's assumption - that the string is interpreted as a date - looks like the explanation you are looking for.
Again, I'm sure the method you found - removing any character of no significance first and then apply the format - is the safe route.
/gustav
>>> Lambert.Heenan at aiuholdings.com 17-07-2009 15:18 >>>
Gustav,
Thank you for those kind words. I always thought that I did not have a clue what I was up to, but it's nice for it to be authoritatively confirmed! :-)
I get what you are saying about the implicit math that is being performed on the string expression passed into Format(), but the results still do not make much sense.
Take the example format("123-45-6789","000-00-0000")
123-45-6789 evaluates to -6711, so one might expect the above call to format to return the string "-000-00-6711", but it does not, it returns "123-45-6789". And yet Format("520-09-0012","000-00-0000") returns the result "-000-50-3778", although the string expression evaluates to 499.
I have resolved my problem, but an curious as to what is actually going on with these weird results.
FYI when I run the original format expression (format([SSN],"000-00-0000") on a list of 15,000 Social Security numbers, only 94 of them wind up be changed in the above manner. All the others behave 'correctly'.
Stuart,
You commented "I've never even considered applying Format to a String before - it's designed to convert numeric values into strings.", but a peak at the OL Help reveals:
To Format
Numbers Use predefined named numeric formats or create user-defined numeric formats.
Dates and times Use predefined named date/time formats or create user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
***Strings*** Create your own user-defined string formats
Lambert