TransferSpreadsheet format question

[Guest]

I am using the TransferSpreadsheet action in Access to send a table to an
excel spreadsheet. The table is made from an action query. At the query
level, a calculation is performed to create a new field that will result in a
numeric field that can contain up to 7 decimal places. (Formatting the fields
at the table level only apply to new records, thus, not affecting the
existing records. Also useless to set up because when the "make table query"
is run, it dumps that table and creates a new one; changes those numeric
fields decimal places to "auto". I can not find a way to set the properties
at the query level either.)

When the table goes to excel, it retains the decimal places. I would like to
reduce all the decimal places to 0, having only whole numbers, either before
it leaves Access or when it gets to Excel. Is there a way to make that
happen?

I am using Office 2000 version for Access and Excel. The TransferSpreadsheet
action is attached to a button on a form and also can be used from a custom
menu (on click event).

Thanks for your help. Donna

 

[Ken Snell [MVP]]

Export a query based on your table. In that query, replace the actual field
with a calculated field where you use the Format function to assign the
format of no decimal places:

NewField: Format([OldFieldName], "0")

 

[Guest]

A couple of things that may help. First, unless you need to have the table
available in Access, it is not necessary to create a table to export to
Excel. You can use a Select Query as the "TableName" argument of the
TransferSpreadsheet method.
If you do need to save the table in Access, then rather than a Make Table
Query, use an Append query instead and delete the data from the table prior
to running the Append query. This way, you can set your formatting as you
need it and it will remain in place. Here is an example:

Sub SendToExcel
Dim dbs as Database

set dbs = CurrentDb
'Empty the data from the table:
dbs.Execute "DELETE * FROM MyTableName;"
'Run the Append Query
dbs.Execute "qappMyQueryName"
'Do your Transfer
DoCmd.TransferSpreadsheet........

 

[Guest]

Thank you, this is exactly what I needed. I forgot that I already had a query
established, in fact, a Totals query is what is being transferred. When I
applied the Format function, Access also made a change after I closed it.
When I reopened in design view it had changed it to

NewField: Sum(Format([OldFieldName], "0")) and changed the "SUM" in the
Totals section to Expression.

Thank you!!!

Export a query based on your table. In that query, replace the actual field
with a calculated field where you use the Format function to assign the
format of no decimal places:

NewField: Format([OldFieldName], "0")
--

Ken Snell
<MS ACCESS MVP>

I am using the TransferSpreadsheet action in Access to send a table to an
excel spreadsheet. The table is made from an action query. At the query
level, a calculation is performed to create a new field that will result
in a
numeric field that can contain up to 7 decimal places. (Formatting the
fields
at the table level only apply to new records, thus, not affecting the
existing records. Also useless to set up because when the "make table
query"
is run, it dumps that table and creates a new one; changes those numeric
fields decimal places to "auto". I can not find a way to set the
properties
at the query level either.)

When the table goes to excel, it retains the decimal places. I would like
to
reduce all the decimal places to 0, having only whole numbers, either
before
it leaves Access or when it gets to Excel. Is there a way to make that
happen?

I am using Office 2000 version for Access and Excel. The
TransferSpreadsheet
action is attached to a button on a form and also can be used from a
custom
menu (on click event).

Thanks for your help. Donna

 

[Guest]

Thanks Klatuu. In this situation, the solution will not work. But, I will
keep this advice handy for another time when it may be more suitable. Thanks!!

 

[Ken Snell [MVP]]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Thank you, this is exactly what I needed. I forgot that I already had a
query
established, in fact, a Totals query is what is being transferred. When I
applied the Format function, Access also made a change after I closed it.
When I reopened in design view it had changed it to

NewField: Sum(Format([OldFieldName], "0")) and changed the "SUM" in the
Totals section to Expression.

Thank you!!!

Export a query based on your table. In that query, replace the actual
field
with a calculated field where you use the Format function to assign the
format of no decimal places:

NewField: Format([OldFieldName], "0")
--

Ken Snell
<MS ACCESS MVP>

I am using the TransferSpreadsheet action in Access to send a table to
an
excel spreadsheet. The table is made from an action query. At the query
level, a calculation is performed to create a new field that will
result
in a
numeric field that can contain up to 7 decimal places. (Formatting the
fields
at the table level only apply to new records, thus, not affecting the
existing records. Also useless to set up because when the "make table
query"
is run, it dumps that table and creates a new one; changes those
numeric
fields decimal places to "auto". I can not find a way to set the
properties
at the query level either.)

When the table goes to excel, it retains the decimal places. I would
like
to
reduce all the decimal places to 0, having only whole numbers, either
before
it leaves Access or when it gets to Excel. Is there a way to make that
happen?

I am using Office 2000 version for Access and Excel. The
TransferSpreadsheet
action is attached to a button on a form and also can be used from a
custom
menu (on click event).

Thanks for your help. Donna