Concatenate data from Imported Excel Spreadsheet

[Guest]

I created a table using SQL then inserted fields from an Excel spreadsheet
into the new table. In the new table, however, I need to combine three of
the fields from the imported spreadsheet to create one field. For many
records, however, Field 2 does not have any data so the term “Field2†is
returned with the combined data.

5000 & ([Field2]) & AA ---- instead of 5000AA

What do I need to do for Field2 when there is not data in it? Placing a 0
in the field will mess up the ConcatField value.

'*********************CODE*******************
dbs.Execute "CREATE TABLE tblAccounts" _
& "( Field1 CHAR (6), Field2 CHAR (4) Field3 (CHAR2), **Concat field*** CHAR
(14)…..

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "tblAccounts2…..

DoCmd.RunSQL "INSERT INTO tblAccounts (Field1, Field2, Field3, *Concatfield*
& "SELECT Field1, Field2, Field3, ([Field1]) & "" & ([Field2]) & "" &
([Field3]) AS Concat field

 

[Guest]

Untested, but try this:
DoCmd.RunSQL "INSERT INTO tblAccounts (Field1, Field2, Field3, *Concatfield*
& "SELECT Field1, Field2, Field3, Nz([Field1],"") & "" & Nz([Field2],"") &
"" &
Nz([Field3],"") AS Concat field