Export crosstab query to Excel: Row hight and Decimal

S

Song Su

In my crosstab query design, I put number in standard with 0 decimal in the
number field. When I export to excel and open it:
1. All the numbers are still 2 decimal
2. Most row hight are 15 except a few rows which is higher

How to fix the crosstab so the Excel would be perfect?

Thanks.
 
J

John Spencer

Please post the SQL of your query.
(Hint: Menu View: SQL)

Please tell use which field(s) have the problem. AS A GUESS, you need to use
CLng or Int around the number to force it to drop the decimal portion.

If you are using the format property on the query, you need to understand that
the format property affects the display of the data and when you export the
query the "display" does not come into play.

You could also use the Format function in the query to force the desired
number of decimal places.

Format([SomeField],"#,###")

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
S

Song Su

Dear John,

I'm trying to show 3 year budget comparison. I use qryUnionBud to put 3 year
tables together.

SELECT * FROM qryBud
UNION ALL
SELECT * FROM qryBudLY
UNION ALL
SELECT * FROM qryBudPR;

In the crosstab field, I set property to standard with 0 decimal. When
export to Excel, it become 2 decimal.

I solved Excel row hight by widen crosstag field before exporting.

Here is my crosstag sql. Thanks.

TRANSFORM Sum(qryUnionBud.C_Budget) AS C_BudgetOfSum
SELECT qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
FROM qryUnionBud
GROUP BY qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
PIVOT qryUnionBud.YYYY;

John Spencer said:
Please post the SQL of your query.
(Hint: Menu View: SQL)

Please tell use which field(s) have the problem. AS A GUESS, you need to
use CLng or Int around the number to force it to drop the decimal portion.

If you are using the format property on the query, you need to understand
that the format property affects the display of the data and when you
export the query the "display" does not come into play.

You could also use the Format function in the query to force the desired
number of decimal places.

Format([SomeField],"#,###")

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Song said:
In my crosstab query design, I put number in standard with 0 decimal in
the number field. When I export to excel and open it:
1. All the numbers are still 2 decimal
2. Most row hight are 15 except a few rows which is higher

How to fix the crosstab so the Excel would be perfect?

Thanks.
Click to expand...
Click to expand...
 
J

John Spencer

PERHAPS the following.

TRANSFORM CLNG(NZ(Sum(qryUnionBud.C_Budget),0)) AS C_BudgetOfSum
SELECT qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
FROM qryUnionBud
GROUP BY qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
PIVOT qryUnionBud.YYYY;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Song said:
Dear John,

I'm trying to show 3 year budget comparison. I use qryUnionBud to put 3 year
tables together.

SELECT * FROM qryBud
UNION ALL
SELECT * FROM qryBudLY
UNION ALL
SELECT * FROM qryBudPR;

In the crosstab field, I set property to standard with 0 decimal. When
export to Excel, it become 2 decimal.

I solved Excel row hight by widen crosstag field before exporting.

Here is my crosstag sql. Thanks.

TRANSFORM Sum(qryUnionBud.C_Budget) AS C_BudgetOfSum
SELECT qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
FROM qryUnionBud
GROUP BY qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
PIVOT qryUnionBud.YYYY;

John Spencer said:
Please post the SQL of your query.
(Hint: Menu View: SQL)

Please tell use which field(s) have the problem. AS A GUESS, you need to
use CLng or Int around the number to force it to drop the decimal portion.

If you are using the format property on the query, you need to understand
that the format property affects the display of the data and when you
export the query the "display" does not come into play.

You could also use the Format function in the query to force the desired
number of decimal places.

Format([SomeField],"#,###")

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Song said:
In my crosstab query design, I put number in standard with 0 decimal in
the number field. When I export to excel and open it:
1. All the numbers are still 2 decimal
2. Most row hight are 15 except a few rows which is higher

How to fix the crosstab so the Excel would be perfect?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
S

Song Su

I replaced my
Sum(qryUnionBud.C_Budget)
with your
CLNG(NZ(Sum(qryUnionBud.C_Budget),0))

Exported to Excel still shows 2 decimal points.

John Spencer said:
PERHAPS the following.

TRANSFORM CLNG(NZ(Sum(qryUnionBud.C_Budget),0)) AS C_BudgetOfSum
SELECT qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
FROM qryUnionBud
GROUP BY qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
PIVOT qryUnionBud.YYYY;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Song said:
Dear John,

I'm trying to show 3 year budget comparison. I use qryUnionBud to put 3
year tables together.

SELECT * FROM qryBud
UNION ALL
SELECT * FROM qryBudLY
UNION ALL
SELECT * FROM qryBudPR;

In the crosstab field, I set property to standard with 0 decimal. When
export to Excel, it become 2 decimal.

I solved Excel row hight by widen crosstag field before exporting.

Here is my crosstag sql. Thanks.

TRANSFORM Sum(qryUnionBud.C_Budget) AS C_BudgetOfSum
SELECT qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
FROM qryUnionBud
GROUP BY qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
PIVOT qryUnionBud.YYYY;

John Spencer said:
Please post the SQL of your query.
(Hint: Menu View: SQL)

Please tell use which field(s) have the problem. AS A GUESS, you need
to use CLng or Int around the number to force it to drop the decimal
portion.

If you are using the format property on the query, you need to
understand that the format property affects the display of the data and
when you export the query the "display" does not come into play.

You could also use the Format function in the query to force the desired
number of decimal places.

Format([SomeField],"#,###")

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Song Su wrote:
In my crosstab query design, I put number in standard with 0 decimal in
the number field. When I export to excel and open it:
1. All the numbers are still 2 decimal
2. Most row hight are 15 except a few rows which is higher

How to fix the crosstab so the Excel would be perfect?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Then I am stuck. I know of no reason that you should get any decimal
places.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Song said:
I replaced my
Sum(qryUnionBud.C_Budget)
with your
CLNG(NZ(Sum(qryUnionBud.C_Budget),0))

Exported to Excel still shows 2 decimal points.

John Spencer said:
PERHAPS the following.

TRANSFORM CLNG(NZ(Sum(qryUnionBud.C_Budget),0)) AS C_BudgetOfSum
SELECT qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
FROM qryUnionBud
GROUP BY qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
PIVOT qryUnionBud.YYYY;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Song said:
Dear John,

I'm trying to show 3 year budget comparison. I use qryUnionBud to put 3
year tables together.

SELECT * FROM qryBud
UNION ALL
SELECT * FROM qryBudLY
UNION ALL
SELECT * FROM qryBudPR;

In the crosstab field, I set property to standard with 0 decimal. When
export to Excel, it become 2 decimal.

I solved Excel row hight by widen crosstag field before exporting.

Here is my crosstag sql. Thanks.

TRANSFORM Sum(qryUnionBud.C_Budget) AS C_BudgetOfSum
SELECT qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
FROM qryUnionBud
GROUP BY qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
PIVOT qryUnionBud.YYYY;

Please post the SQL of your query.
(Hint: Menu View: SQL)

Please tell use which field(s) have the problem. AS A GUESS, you need
to use CLng or Int around the number to force it to drop the decimal
portion.

If you are using the format property on the query, you need to
understand that the format property affects the display of the data and
when you export the query the "display" does not come into play.

You could also use the Format function in the query to force the desired
number of decimal places.

Format([SomeField],"#,###")

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Song Su wrote:
In my crosstab query design, I put number in standard with 0 decimal in
the number field. When I export to excel and open it:
1. All the numbers are still 2 decimal
2. Most row hight are 15 except a few rows which is higher

How to fix the crosstab so the Excel would be perfect?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
S

Song Su

I got it!
For my Sum(qryUnionBud.C_Budget) field property, I put format as #,### with
decimal point to 0. Both result of crosstab and exported Excel shows ,
format with no decimal.

John Spencer said:
Then I am stuck. I know of no reason that you should get any decimal
places.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Song said:
I replaced my
Sum(qryUnionBud.C_Budget)
with your
CLNG(NZ(Sum(qryUnionBud.C_Budget),0))

Exported to Excel still shows 2 decimal points.

John Spencer said:
PERHAPS the following.

TRANSFORM CLNG(NZ(Sum(qryUnionBud.C_Budget),0)) AS C_BudgetOfSum
SELECT qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
FROM qryUnionBud
GROUP BY qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
PIVOT qryUnionBud.YYYY;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Song Su wrote:
Dear John,

I'm trying to show 3 year budget comparison. I use qryUnionBud to put 3
year tables together.

SELECT * FROM qryBud
UNION ALL
SELECT * FROM qryBudLY
UNION ALL
SELECT * FROM qryBudPR;

In the crosstab field, I set property to standard with 0 decimal. When
export to Excel, it become 2 decimal.

I solved Excel row hight by widen crosstag field before exporting.

Here is my crosstag sql. Thanks.

TRANSFORM Sum(qryUnionBud.C_Budget) AS C_BudgetOfSum
SELECT qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
FROM qryUnionBud
GROUP BY qryUnionBud.Typ, qryUnionBud.Fund, qryUnionBud.FundDescription
PIVOT qryUnionBud.YYYY;

Please post the SQL of your query.
(Hint: Menu View: SQL)

Please tell use which field(s) have the problem. AS A GUESS, you need
to use CLng or Int around the number to force it to drop the decimal
portion.

If you are using the format property on the query, you need to
understand that the format property affects the display of the data
and when you export the query the "display" does not come into play.

You could also use the Format function in the query to force the
desired number of decimal places.

Format([SomeField],"#,###")

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Song Su wrote:
In my crosstab query design, I put number in standard with 0 decimal
in the number field. When I export to excel and open it:
1. All the numbers are still 2 decimal
2. Most row hight are 15 except a few rows which is higher

How to fix the crosstab so the Excel would be perfect?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Cannot export results of crosstab query 1
Crosstab query > make table 3
Crosstab Query Exported from Access to Excel 1
Limiting Decimal Places in a Crosstab Union Query 1
Conditional Formatting in a CrossTab Query 1
Crosstab & Query by Form 1
Crosstab query oddity 5
Exporting crosstab query to excel 1

Top