Type Conversion error

[voskouee]

i am importing from excel and i have a column that has account numbers and at some point i have an account like this... 2C1C18100

on the import i get an error.

i set the field to memo, numner or text and still get the same error..

any suggestions?

thanks in advance

 

[Bodisathva]

is the import an append query or a make table query?

If you are running a make table query, Access defines the field types based upon the first bit of info to be inserted.

 

[voskouee]

i am importing using vb.. and it works fine except that field.. i dont want to change it.. is there a way to modify this?


ub Import_TD()

Dim i As Integer
With Application.FileSearch
.LookIn = "c:\Input Files\TD"
.SearchSubFolders = False
.FileName = "*.xls"
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" TD file(s) found."

DoCmd.SetWarnings False
For i = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(i)
' Workbooks.Open Filename:=.FoundFiles(i)

DoCmd.TransferSpreadsheet acImport, 8, "TD_Input", .FoundFiles(i), True, ""

Next i
DoCmd.SetWarnings True

Else
MsgBox "There were no files found."

End If
End With

CurrentDb.Execute "Delete_empty lines_TD", dbFailOnError
' Delete empty lines and disable Error message
CurrentDb.Execute "TD_Append Query", dbFailOnError
'Append to working table

End Sub

is the import an append query or a make table query?

If you are running a make table query, Access defines the field types based upon the first bit of info to be inserted.

 

[Moniker]

The way you are doing it is a Make Table query, which is the problem that was described earlier. It sees a Numeric value first and sets the field type to Numeric. To get around this, go to your source Excel sheet, highlight the entire row that contains account numbers, and change the formatting to Text.

 

[boblarson]

This has nothing to do with your current problem, but just an FYI for you:

In your line -
DoCmd.TransferSpreadsheet acImport, 8, "TD_Input", .FoundFiles(i), True, ""
You do not need the comma and "" after True. Just leave it off. Also, I'm surprised that it works with just 8 in the excel version line as it has always made me put in acSpreadsheetTypeExcel8.

 

[Moniker]

Nitpicking here, but acSpreadsheetTypeExcel8 has a constant value of 8, which is just coincidental in this case.

[u]Constant[/u]                   [u]Value[/u] 
acSpreadsheetTypeExcel3    0 
acSpreadsheetTypeExcel4    6 
acSpreadsheetTypeExcel5    5 
acSpreadsheetTypeExcel7    5 
acSpreadsheetTypeExcel8    8 
acSpreadsheetTypeExcel9    8 
acSpreadsheetTypeLotusWJ2  4 
acSpreadsheetTypeLotusWK1  2 
acSpreadsheetTypeLotusWK3  3 
acSpreadsheetTypeLotusWK4  7

 

[boblarson]

that's not nitpicking; that's good info.

 

[Moniker]

I'll post this somewhere more appropriate later on, but I just made a dinky DB that contains all the object names, their constants, and that constant's particular value. For reference to anyone reading this, it uses a subform whos data is controlled by a drop-down on the main form (that question comes up 10 times a day), and I even bothered to normalize the thing. And, I still have 35 minutes left for lunch!

To use it, just open the form "f_Main" and select the name of the object for which you want the constants and their values. (The one I posted above is "AcSpreadSheetType".) Enjoy.

 

[theprez]

Bodisathva was on point here. On importing, Access uses the first couple of records to determine the data type of the remainder rows. I don't know if this is an option, but I often include a dummy record at the top that forces the format. For the field in question, you could insert a dummy record at the top and insert "XXXXX" for example to force the import to be text.

If this is some automated process, you can right a delete query that looks for the "XXXXXX" in the field in question and delete the record from the newly made table. Or you can delete it manually.

 

[DC1]

Moniker,

Thank you very much for that service. I have already downloaded the mdb file to a place I can easily access it for future reference.

DC1