[AccessD] Format wiedness - was - TransferText wierdness

It seems to be the second and/or third 0


?Format("520-09-1012","000-00-0000")
520-09-1012

?Format("520-19-0012","000-00-0000")
520-19-0012

?Format("520-09-0012","???-??-????")
520-09-1012

?Format("520-09-0012","???-??-????")
-???-??-????

weird




On Thu, Jul 16, 2009 at 2:38 PM, Gustav Brock <Gustav at cactus.dk> wrote:

> 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
>