TransferText converting some CSV cells to blanks (A2003 with Excel 2003)

[PW]

Hi,

When I run the following command, some fields are ending up blank when
the clearly have values them in Excel. I have tried converting the
columns to general and text. The ones that are ending up blank (Null,
actually) contain:

lu24769884l
lu24769884xxl

Just text. Displayed the same value up top when I click on that cell
in Excel.

The values that do get transposed correctly are all numeric as
617867094518 but that one displayed as 6.17867E+1 in it's cell.
However, that cell gets converted properly into the Access table.

My transfertext code:

strFind = "[date] = #" & varFileDate & "# And [purchaser] = " &
intPurchaser

varReturnVal = DLookup("[date]", "tblInventoryPurchases", strFind)
DoCmd.TransferText acImportDelim, , "tblTempCashReg", strReturnVal, -1

This particular field in the Access table is formatted as text.
Any ideas?

-paul

[Tom van Stiphout]

On Sun, 18 May 2008 20:10:21 -0600, PW
<pa**********************@removehotmail.comwrote :

I think export code typically only looks at the first few lines to
determine the data type. In your case it guessed wrong and can't
import an alphanumeric value in a numeric column.
Better to first create the table, attach the file, and import it using
an Append query.

-Tom.

>Hi,

When I run the following command, some fields are ending up blank when
the clearly have values them in Excel. I have tried converting the
columns to general and text. The ones that are ending up blank (Null,
actually) contain:

lu24769884l
lu24769884xxl

Just text. Displayed the same value up top when I click on that cell
in Excel.

The values that do get transposed correctly are all numeric as
617867094518 but that one displayed as 6.17867E+1 in it's cell.
However, that cell gets converted properly into the Access table.

My transfertext code:

strFind = "[date] = #" & varFileDate & "# And [purchaser] = " &
intPurchaser

varReturnVal = DLookup("[date]", "tblInventoryPurchases", strFind)
DoCmd.TransferText acImportDelim, , "tblTempCashReg", strReturnVal, -1

This particular field in the Access table is formatted as text.
Any ideas?

-paul