Converting numbers to text in an Insert Statement

[Rafi]

I an appneding records to an Access table from an Excel file where the data
in the Zip Code column is not consistent; some is numeric while others are
text. The corresponding field in Access is defined as a text field. My
problem is that tthe resulting records have a null value for Zip Code for
those records that were formated as numeric in Excel. My code is listed
below. How can I make sure the data is converted to text and appended to my
table?

Thx.

stSQL = "INSERT INTO TblRecords( CoName, [Address 1], [Address 2], City, " _
& "State, [Postal Code], [Postal Code Plus Four], " _
& "Account, Segment) " _
& "SELECT DHL.Name, DHL.[Address 1], DHL.[Address 2], " _
& "DHL.City, DHL.State, Format(DHL.[Postal Code],""00000"")," _
& "DHL.[Postal Code Plus Four], " _
& "DHL.Account,""" & stSeg & """" _
& " FROM DHL;"

 

[Jeanette Cunningham]

Rafi,
try using the convert to string function CStr

Replace
Format(DHL.[Postal Code],""00000"")
with
CStr(Format(DHL.[Postal Code],""00000""))

Jeanette Cunningham

 

[Jeanette Cunningham]

Rafi,
try using the convert to string function CStr

You might need to put the CStr function closer to the PostalCode field like
this

Replace
Format(DHL.[Postal Code],""00000"")
with
Format(CStr(DHL.[Postal Code]),""00000"")

the code will first convert the postal code to a string
CStr(DHL.[Postal Code])
then format it.

Jeanette Cunningham

 

[Rafi]

Works like a charm !!!!!!! Thanks

Rafi,
try using the convert to string function CStr

Replace
Format(DHL.[Postal Code],""00000"")
with
CStr(Format(DHL.[Postal Code],""00000""))

Jeanette Cunningham

I an appneding records to an Access table from an Excel file where the data
in the Zip Code column is not consistent; some is numeric while others are
text. The corresponding field in Access is defined as a text field. My
problem is that tthe resulting records have a null value for Zip Code for
those records that were formated as numeric in Excel. My code is listed
below. How can I make sure the data is converted to text and appended to
my
table?

Thx.

stSQL = "INSERT INTO TblRecords( CoName, [Address 1], [Address 2], City, "
_
& "State, [Postal Code], [Postal Code Plus Four], " _
& "Account, Segment) " _
& "SELECT DHL.Name, DHL.[Address 1], DHL.[Address 2], " _
& "DHL.City, DHL.State, Format(DHL.[Postal Code],""00000"")," _
& "DHL.[Postal Code Plus Four], " _
& "DHL.Account,""" & stSeg & """" _
& " FROM DHL;"