Possibe to use "NewField: CDbl([ExistingField])" in a CROSS-TAB Query ??

[kev100 via AccessMonster.com]

(Please Pardon this Double Post....the first was mistakenly posted to the
wrong forum)

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

 

[Duane Hookom]

It would have helped if you had just posted your crosstab sql view. However,
you might be able to wrap your "value" expression in the Val() function.

 

[kev100 via AccessMonster.com]

Duane,

Thanks very much.

I had continued fiddling with this process and found that creating a basic
query based on the Cross-Tab query does allow the CDbl to be used. However,
this is the long-way-round, I'm sure.

But, with the val() function, would that be: val(NewField: CDbl(
[ExistingField])) ??

Here's that code (this is the same code you provided earlier, but simply used
with different data....remember.....it also Groups certain totals together
into one column?.....Which btw...is beginning to REALLY pay off....thanks
again...very much).

TRANSFORM Count(ALL_Temp.country) AS CountOfcountry
SELECT ALL_Temp.route, Sum(Abs(InStr("YS,NO",[Termcd])=0)) AS OTHER
FROM ALL_Temp
WHERE (((ALL_Temp.route)<>"" And (ALL_Temp.route)<>"0") AND ((ALL_Temp.termcd)
<>"00"))
GROUP BY ALL_Temp.route
ORDER BY ALL_Temp.route
PIVOT ALL_Temp.termcd In ("YS","NO");

It would have helped if you had just posted your crosstab sql view. However,
you might be able to wrap your "value" expression in the Val() function.

(Please Pardon this Double Post....the first was mistakenly posted to the
wrong forum)

[quoted text clipped - 40 lines]

Thanks very much.....

 

[Duane Hookom]

From your SQL view, which field is text and stores numbers? Which column(s)
send text rather than numeric into Excel?

--
Duane Hookom
MS Access MVP
--

Duane,

Thanks very much.

I had continued fiddling with this process and found that creating a basic
query based on the Cross-Tab query does allow the CDbl to be used.
However,
this is the long-way-round, I'm sure.

But, with the val() function, would that be: val(NewField: CDbl(
[ExistingField])) ??

Here's that code (this is the same code you provided earlier, but simply
used
with different data....remember.....it also Groups certain totals together
into one column?.....Which btw...is beginning to REALLY pay off....thanks
again...very much).

TRANSFORM Count(ALL_Temp.country) AS CountOfcountry
SELECT ALL_Temp.route, Sum(Abs(InStr("YS,NO",[Termcd])=0)) AS OTHER
FROM ALL_Temp
WHERE (((ALL_Temp.route)<>"" And (ALL_Temp.route)<>"0") AND
((ALL_Temp.termcd)
<>"00"))
GROUP BY ALL_Temp.route
ORDER BY ALL_Temp.route
PIVOT ALL_Temp.termcd In ("YS","NO");

It would have helped if you had just posted your crosstab sql view.
However,
you might be able to wrap your "value" expression in the Val() function.

(Please Pardon this Double Post....the first was mistakenly posted to
the
wrong forum)

[quoted text clipped - 40 lines]

Thanks very much.....

 

[kev100 via AccessMonster.com]

From your SQL view, which field is text and stores numbers? Which column(s)
send text rather than numeric into Excel?

The column with ALL_Temp.route data.

ALL_Temp.route is actually a route #. For some reason...when the table is
created (a process over which I have no control) it is set as text.....for
some reason.

 

[Duane Hookom]

TRANSFORM Count(country) AS CountOfcountry
SELECT Val(route) as Rte , Sum(Abs(InStr("YS,NO",[Termcd])=0)) AS OTHER
FROM ALL_Temp
WHERE Nz(route,0)<>0 AND termcd <>"00"
GROUP BY Val(route)
ORDER BY Val(route)
PIVOT ALL_Temp.termcd In ("YS","NO");