Heenan, Lambert
Lambert.Heenan at aiuholdings.com
Thu Jul 16 16:14:14 CDT 2009
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
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Thursday, July 16, 2009 4:23 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] TransferText wierdness
Well, all I can say is it's been that way as long as I recall. It assumes that whatever kind of value is in the first row, that's the datatype for the column, assuming it can figure out how many columns it has. 'Course it also depends on what kind of delimiter the text file uses. Commas don't work as well as pipes, and spaces are ghastly.
Charlotte
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, July 16, 2009 11:17 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] TransferText wierdness
LOL. It is an optional parameter...
Indeed, once I created the import spec it worked just fine.
I had a permanently defined destination table that I was importing into, all fields defined as text, but apparently the TransferText function isn't capable of looking at the destination field to see what the expected data type is. Transfer text is delivering ALL text data, but is just trashing some of the data (not delivering it into the field in the destination table) unless it is spoon fed an import specification up front.
John W. Colby
www.ColbyConsulting.com
Charlotte Foust wrote:
> That's why you create import/export specs, John. You know that.
>
> Charlotte
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, July 16, 2009 7:26 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] TransferText wierdness
>
> You will love this one.
>
> Place the following text into a text file and run a transfer text on
it:
>
> R4inc quarter,20083,,,,,,
> NDC,Drug Name,Rebate Amt Due,#Rx,State Paid,Quantity,Formula,Unit
> Price
>
> DoCmd.TransferText acImportDelim , , "RawFormat",
> "YourPath\YourName.csv"
>
> the following is what I get:
>
> PK F1 F2 F3 F4 F5 F6 F7 F8
> F9 F10 F11 F12
> 26361 R4inc quarter 20083
>
> 26362 NDC Rebate Amt Due #Rx State Paid Quantity
> Formula Unit Price
> Notice that the "Drug Name" data item in the second row dropped out.
>
> Now, try this one:
>
> R4inc quarter,20083,,,,,,
> NDC,Drug Name,Rebate Amt Due,#Rx,State Paid,Quantity,Formula,Unit
Price
> 597001314,COMBIVENT AER,0,5,513.01,73.5,2: 7.66735 - 3.1734 -
> 4.6244,0
>
>
>
> The following is what I get.
>
> F1 F2 F3 F4 F5 F6 F7 F8 F9
> F10 F11 F12
> R4inc quarter 20083
>
> NDC Drug Name Formula
>
> 597001314 COMBIVENT AER 0 5 513.01 73.5 2:
> 7.66735 - 3.1734 - 4.6244 0
> Notice the dropping of TONS of data items in the "field names" row.
>
> But hey, why fix the bugs in Access when there are pretty toolbars
> that need designing eh?
>
> 8(
>
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com