[AccessD] Format wiedness - was - TransferText wierdness

Stuart McLachlan stuart at lexacorp.com.pg
Thu Jul 16 17:14:08 CDT 2009


I've never even considered applying Format to a String before - it's designed to convert 
numeric values into strings.  

Access is trying to evaluate "520-09-0012" as some sort of valid number or date.
It decided that it could interpret it as a date (yyyy-mm-dd).

Try Format("520-09-0012","d mmm yyyy") and you will get "12 Sep 520"
and 
Format("12 Sep 520","000000") =  -503778

-- 
Stuart

On 16 Jul 2009 at 17:14, Heenan, Lambert wrote:

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