K
kev100 via AccessMonster.com
(This is a re-edit from a previous post...but for a related question which is
similar...but this situation uses a Cross-Tab Query, rather than a basic
query)
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 no
control over its creation.
I have a report based on a Cross-Tab Query which is very often "Output To" an
XLS
format via a Macro (it 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 for this to be done automatically when the
macro/query is run.
In a previous situation using a Basic query....the solution was to create
another column using the conversion function "NewField: CDbl([ExistingField]
)"
This worked very well, in that it simply referred to the main field (column)
in the query and I simply displayed that conversion field in the results.
However, with a Cross-Tab query...information is not displayed in simply a
line- for-each-record manner....this Cross-Tab Query totals things up.
However.....that column that is "number stored as text" remains a problem in
the output (with sorting, math, etc). Is there a way to used that
converstion function "NewField: CDbl([ExistingField])" in a cross tab query
to solve this?
Thanks very much.....
similar...but this situation uses a Cross-Tab Query, rather than a basic
query)
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 no
control over its creation.
I have a report based on a Cross-Tab Query which is very often "Output To" an
XLS
format via a Macro (it 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 for this to be done automatically when the
macro/query is run.
In a previous situation using a Basic query....the solution was to create
another column using the conversion function "NewField: CDbl([ExistingField]
)"
This worked very well, in that it simply referred to the main field (column)
in the query and I simply displayed that conversion field in the results.
However, with a Cross-Tab query...information is not displayed in simply a
line- for-each-record manner....this Cross-Tab Query totals things up.
However.....that column that is "number stored as text" remains a problem in
the output (with sorting, math, etc). Is there a way to used that
converstion function "NewField: CDbl([ExistingField])" in a cross tab query
to solve this?
Thanks very much.....