data type conversion when automating excel from access

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!
 
G

Guest

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
 
G

Guest

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.
 
G

Guest

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
 
G

Guest

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
 
E

Ed Adamthwaite

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top