Fixed Numbers

[Melissa]

I have a table that is imported from Excel with percentages and numbers. This
table changes weekly and is used in conjunction with existing tables to
create a query. I export the query to Excel to run a mail merge. Previously,
with Access 2003, I could export the file to Excel and the formatting would
remain in tact. Now, with Access 2007, when I export the data, the numbers
are not converting to the format I need them to.

I have percentage fields that when I import from the original Excel file
look like this: 77.1322291166201%
I had this setup to import as text so the query would export the number as
77%. Now when I run this query the number is 77.1322291166201%.

I have number fields that when I import from the original Excel file look
like this:
18.1834858828378
I also had this setup to import as text so the query would export the number
as 18.2. Now when I run the query the number is 18.1834858828378

Is there anyway to change the query so it is a fixed number when exported
back to Excel? Sorry, if I am not asking the question in the right way. I am
not very familiar with Access and have very little exposure to the
application.

Melissa

 

[John W. Vinson]

I have a table that is imported from Excel with percentages and numbers. This
table changes weekly and is used in conjunction with existing tables to
create a query. I export the query to Excel to run a mail merge. Previously,
with Access 2003, I could export the file to Excel and the formatting would
remain in tact. Now, with Access 2007, when I export the data, the numbers
are not converting to the format I need them to.

I have percentage fields that when I import from the original Excel file
look like this: 77.1322291166201%
I had this setup to import as text so the query would export the number as
77%. Now when I run this query the number is 77.1322291166201%.

I have number fields that when I import from the original Excel file look
like this:
18.1834858828378
I also had this setup to import as text so the query would export the number
as 18.2. Now when I run the query the number is 18.1834858828378

Is there anyway to change the query so it is a fixed number when exported
back to Excel? Sorry, if I am not asking the question in the right way. I am
not very familiar with Access and have very little exposure to the
application.

Melissa

You can and should do the mail merge, not with the imported table, but with a
Query based on that table.

In that query you can explicitly format the number fields. For example, if
your percentage field is MyPCT, you could type

ExpMyPCT: Format(MyPCT*100, "#0%")

The number is actually stored as 0.77132229... so multiplying by 100 will give
you a percentage, and the Format expression will limit it to two digits.

You can do the same for the other number field - Format([fieldname], "#.0")
will work.

 

[Melissa]

Thank you so much John!!!! That worked perfectly! Have a great day!
--
Melissa

I have a table that is imported from Excel with percentages and numbers. This
table changes weekly and is used in conjunction with existing tables to
create a query. I export the query to Excel to run a mail merge. Previously,
with Access 2003, I could export the file to Excel and the formatting would
remain in tact. Now, with Access 2007, when I export the data, the numbers
are not converting to the format I need them to.

I have percentage fields that when I import from the original Excel file
look like this: 77.1322291166201%
I had this setup to import as text so the query would export the number as
77%. Now when I run this query the number is 77.1322291166201%.

I have number fields that when I import from the original Excel file look
like this:
18.1834858828378
I also had this setup to import as text so the query would export the number
as 18.2. Now when I run the query the number is 18.1834858828378

Is there anyway to change the query so it is a fixed number when exported
back to Excel? Sorry, if I am not asking the question in the right way. I am
not very familiar with Access and have very little exposure to the
application.

Melissa

You can and should do the mail merge, not with the imported table, but with a
Query based on that table.

In that query you can explicitly format the number fields. For example, if
your percentage field is MyPCT, you could type

ExpMyPCT: Format(MyPCT*100, "#0%")

The number is actually stored as 0.77132229... so multiplying by 100 will give
you a percentage, and the Format expression will limit it to two digits.

You can do the same for the other number field - Format([fieldname], "#.0")
will work.

 

[Piet Linden]

I have a table that is imported from Excel with percentages and numbers. This
table changes weekly and is used in conjunction with existing tables to
create a query. I export the query to Excel to run a mail merge. Previously,
with Access 2003, I could export the file to Excel and the formatting would
remain in tact. Now, with Access 2007, when I export the data, the numbers
are not converting to the format I need them to.

I have percentage fields that when I import from the original Excel file
look like this: 77.1322291166201%
I had this setup to import as text so the query would export the number as
77%. Now when I run this query the number is 77.1322291166201%.

I have number fields that when I import from the original Excel file look
like this:
18.1834858828378
I also had this setup to import as text so the query would export the number
as 18.2. Now when I run the query the number is 18.1834858828378

Is there anyway to change the query so it is a fixed number when exported
back to Excel? Sorry, if I am not asking the question in the right way. Iam
not very familiar with Access and have very little exposure to the
application.

Melissa

Use ROUND(number, significant digits). Create a query to round your
numbers to the desired number of decimal places, then export the
result of the query to Excel. Ken Snell has tons of examples on his
website.