CONVERT Numbers-As-Text to PURE Numbers when converting to xls ??

[kev100 via AccessMonster.com]

I have a table that has a text field which stores only numbers. I'm not sure
why it is formatted as text...but it always is...and I currently have to
control over its creation.


I have a report based on a query which is very often "Output To" an XLS
format via a Macro (it very cooly works with one click) so it can be used in
a spreadsheet.

Well...that text field with numbers comes into excel as text, of course. At
least the excel indicates so with little triangles in the cells' corners.
Excel also provides a little pull down mention which allows a quick convert
to true numbers.

BUT....it would be GREAT to be able to do this on-the-fly when it converts to
Excel via that Macro. This would make a huge difference.

Does anyone know if this is possible (e.g. a setting in the output-to feature,
etc) ?

Thanks

 

[John Nurick]

Create a query that uses a conversion function in a calculated field,
and export the query instead of the table. E.g.

NewField: CDbl([ExistingField])

 

[kev100 via AccessMonster.com]

Create a query that uses a conversion function in a calculated field,

and export the query instead of the table. E.g.

NewField: CDbl([ExistingField])

John,

Thanks very much for that tip. Sorry for not responding sooner.

I'm kinda new to Access and am learning bit by bit. I mainly use the wizards
and pull-down functions...but several folks here have help greatly with SQL
scripts.

My table has a field in it contain numbers....use 1-300 or so. The field,
the creation of which is beyond my control, is a 4 character text field....
for some unknown reason.

The field name is "route" the table name is "addrdata"

I have a query which references that field, and a few others.

That field is the only one which need to be converted to a number. I have a
Macro which runs that query with the "output to to excel format" option.

I am not sure how to use "NewField: CDbl([ExistingField])" when creating the
query. Does that line go in of the the fields in Design View of the query....
or is it entered in the SQL view?

and....would the command/instruction be: CDbl (.addrdata.route) ?

Thanks

 

[kev100 via AccessMonster.com]

I am not sure how to use "NewField: CDbl([ExistingField])" when creating the

query. Does that line go in of the the fields in Design View of the query....
or is it entered in the SQL view?

and....would the command/instruction be: CDbl (.addrdata.route) ?

Please forgive my too-quick reply.

I just took a moment to really look over the Design View layout and saw what
you were referring to.

It seems to work great. I'll give it a try tomorrow on a few of the existing
query apps.

Thanks VERY much...this will make a big difference. We'll actually be able
to sort now and have 2 follow 1 (rather than 10)

 

[steven]

I have a table that has a text field which stores only numbers. I'm not sure
why it is formatted as text...but it always is...and I currently have to
control over its creation.


I have a report based on a query which is very often "Output To" an XLS
format via a Macro (it very cooly works with one click) so it can be used in
a spreadsheet.

Well...that text field with numbers comes into excel as text, of course. At
least the excel indicates so with little triangles in the cells' corners.
Excel also provides a little pull down mention which allows a quick convert
to true numbers.

BUT....it would be GREAT to be able to do this on-the-fly when it converts to
Excel via that Macro. This would make a huge difference.

Does anyone know if this is possible (e.g. a setting in the output-to feature,
etc) ?

Thanks

 

[John Nurick]

Hi Steven,

Try creating a query that uses a calculated field to convert the
"numbers-as-text" to a numeric data type. E.g.

NumNumber: CDbl([TextNumber])

Then export the query.