[AccessD] Format wiedness - was - TransferText wierdness

Gustav,

I don't think this is Lamberts problem, but I'm sure this is a case for your
date-expertise:
?Format("520-09-0012","000-00-0000")
-000-50-3778
?Format("520-13-0012","000-00-0000")
520-13-0012

The second section seems to trigger VB to assume a date for 09 (month) or a
non-date for 13 (plain text).

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Gustav Brock
Sendt: 16. juli 2009 23:38
Til: accessd at databaseadvisors.com
Emne: 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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com