[AccessD] Format wierdness - was - TransferText wierdness

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