data type conversion when automating excel from access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

To all,

I'm working on automating data from a query in Access to Excel and having a
difficulty in formatting the data.

Basically, I want to display any zip code that begins with zero as a text in
Excel from Access. Does anyone know how to do that? I really appreciate your
help. Thanks!
 
Hi Tanya,
in the query you use to export the data from access to XL add a cstr to the
field you wanna be exported as text. e.g. zip: cstr(zip code)

HTH Paolo
 
I tried to put "" or cstr in front of the expression but it doesn't work.

However, surprisingly Excel displays the data as a text, when I tried to put
a single quote concatenate with the expression on my VBA code in Access.

Thanks Ed and Paolo for your advice.
 
Hey Tanya,
I would say no surprise 'cause the single quote is the indicator for XL to
"understand" the expression as a text... is what Ed told you
You can use the cstr before the field, as I told you, if you export a saved
query (and you put the cstr in front of the field in the query structure) to
XL but if you work in VBA the single quote is the best way.
Regards Paolo
 
Thank you!

Paolo said:
Hey Tanya,
I would say no surprise 'cause the single quote is the indicator for XL to
"understand" the expression as a text... is what Ed told you
You can use the cstr before the field, as I told you, if you export a saved
query (and you put the cstr in front of the field in the query structure) to
XL but if you work in VBA the single quote is the best way.
Regards Paolo
Click to expand...
 
Hi Tania
To have leading zeros in Excel they must be prefixed with a quote "'".
When entering the data into a cell, do something like:

If rs.Fields(i).Name = "Zipcode" then
Cells(RowNum,ColumNum) = "'" & rs.Fields(i)
Else
Cells(RowNum,ColumNum) = rs.Fields(i)
End If

Regards,
Ed.
 
Back
Top