Fixed Numbers


M

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
 
Ad

Advertisements

J

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

Melissa

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


John W. Vinson said:
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.
 
Ad

Advertisements

P

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.
 

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

Top