By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,182 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Change numerical data

P: n/a
max
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.

Oct 3 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
max wrote:
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.
Perhaps you will need accuracy to five decimal places sometime? You
could maintain this and still link the tables using
JOIN ON Round(Table1.FieldName,1) = Table2.FieldName

If you make a new table or change the values in the original table you
may very well run into problems with keeping your data current.

Oct 3 '06 #2

P: n/a
It doesn't usually make much (logical) sense to use a floating point number
as a key or a join field. Identity fields are most often either Integer or
Text. I'd suggest you take a careful look at the design of your database.

Larry Linson
Microsoft Access MVP
"max" <ms****@yahoo.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.

Oct 3 '06 #3

P: n/a
Run an update query that rounds your data; or, without doing damage,
create a view (a select query) on your table where you round the number.

To return just one decimal, I use this expression:

round(yourfield*10+.5)/10

max schreef:
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.
--
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html
Oct 3 '06 #4

P: n/a
max
Thanks to all who responded.

The JOIN ON Round(Table1.FieldName,1) = Table2.FieldName solution
gives me what I need. Thanks again.
max wrote:
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.
Oct 4 '06 #5

P: n/a

Larry Linson wrote:
It doesn't usually make much (logical) sense to use a floating point number
as a key or a join field. Identity fields are most often either Integer or
Text. I'd suggest you take a careful look at the design of your database.

Larry Linson
Microsoft Access MVP
unless you are querying ranges of numbers for display in a report,
though this doesn't seem to be the case here since the writer is
willing to discard these floating decimals.

Oct 4 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.