Type Conversion Failure

[fpmsi]

I have a excel file that is being imported (appended) to a already created table monthly. I am receiving the "Type Conversion Failure" in the Zip Code field.



All zip codes are being imported except for a few with the following data examples:
OX135dJ
01730-1846

The zipcode field in the table is set as just "text".


Any help would be appreciated. thanks.

[June7]

I just tested importing the sample data you posted and it works. However, I did this in Access2010 but expect would have same result with Access2007. Provide your Excel file for analysis. Remove confidential data.

[RuralGuy]

Zip codes should really be a Text field but Access will try and import a field that starts as a number as a Double unless you make sure *all* of the column is Text in Excel.

[June7]

RuralGuy's suggestion is a possible issue. Are you using the import wizard? I just tested import where the first row of data was an all-numeric zip code and the import wizard set the field as text anyway and all records imported.

[fpmsi]

Thanks Guys - If i format the column in excel to "text" before importing it seems to work fine.

This is being imported through a form not an import wizard so I guess now i need to see if i can control that column before import. As I am not sure that the client is going to want to have to format cells before he imports his file.

Can Access format a column before import?

Using a basic filelist for him to select a file and import:

DoCmd.TransferSpreadsheet acImport, , _
"tblMailing", FileList.Value, True, ""

[June7]

Yes, you can use VBA to set properties of worksheet cells. Just not sure about the specific syntax. Google: vba excel set cell formt - bet you will get results. Or use the macro recorder to get some code and then clean it up.

[fpmsi]

Recorded the macro and received:

Sub Macro1()
Columns("H:H").Select
Selection.NumberFormat = "@"
End Sub

Not sure how to use that during import from access form button. Also would need to change (H:H) to find "Zip Code" field in case it moved.

[June7]

Running the code from Access involves declaring and setting an Excel app object variable and worksheet object variable. Google, there is lots out there.