[AccessD] Format wierdness - was - TransferText wierdness

Gustav Brock Gustav at cactus.dk
Fri Jul 17 09:02:58 CDT 2009


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


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, July 16, 2009 5:38 PM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Format wiedness - was - TransferText wierdness

Hi Lambert

That's because you don't know what you are doing! 
You deal with the SSN as if it was a number which it isn't - it's a string. Thus:

SSN = Format("123456789","&&&-&&-&&&&")

"123-45-6789" confuses Format as it believes it to be a negative number. 
This, however, will format "correctly":

"-123-45-6789"

but is that less weird? I guess not.

Your method of removing any non important character like space and hyphen before applying the format is the proven method.

/gustav


>>> Lambert.Heenan at aiuholdings.com 16-07-2009 23:14 >>>

Here's another oddity.

I have been using the Format() function to ensure that Social Security numbers are consistently stored to a text field with the hyphens included: nnn-nn-nnnn. (I know, you can use an input mask for that, but it does not consistently work)

So this code should take care of that

SSN = Format("123-45-6789","000-00-0000"), and that does indeed give the result "123-45-6789", as does SSN = Format("123456789","000-00-0000")

But what about Format("520-09-0012","000-00-0000")? No that does not result in "520-09-0012", but rather it produces "-000-50-3778"  - go figure.

My solution to this problem is to first strip out all the hyphens and then put them back with...

Format(Replace([SSN],"-",""),"000-00-0000")

But what are those bizarre results about?

Lambert





More information about the AccessD mailing list