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

"Tanya" wrote:

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

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.

"Ed Adamthwaite" wrote:

> 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.
>
> "Tanya" <(E-Mail Removed)> wrote in message
> news:8CB66D8F-D002-4488-A1EF-(E-Mail Removed)...
> > 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!
>

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

"Tanya" wrote:

> 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.
>
> "Ed Adamthwaite" wrote:
>
> > 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.
> >
> > "Tanya" <(E-Mail Removed)> wrote in message
> > news:8CB66D8F-D002-4488-A1EF-(E-Mail Removed)...
> > > 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!
> >

Thank you!

"Paolo" wrote:

> 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
>
> "Tanya" wrote:
>
> > 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.
> >
> > "Ed Adamthwaite" wrote:
> >
> > > 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.
> > >
> > > "Tanya" <(E-Mail Removed)> wrote in message
> > > news:8CB66D8F-D002-4488-A1EF-(E-Mail Removed)...
> > > > 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 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.

"Tanya" <(E-Mail Removed)> wrote in message
news:8CB66D8F-D002-4488-A1EF-(E-Mail Removed)...
> 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!