Number of decimal places output

[LynMVM]

I have several databases where we measure and store various variables but
from these calculate others. For example we input date of birth and date of
questionnaire and then work with age at time of data collection. As there are
a large number of these variables and quite a few similar databases each time
I need to update the calculated variables using my queries they calculate the
results to 13 decimal places. I have specified the number of decimal places
in the resulting table and theat works OK but whenever I add a new variable
which I do frequently it deletes the original table and so reverts to 13
decimal places. I am bored of looking up the number of decimal places each
variable needs to have varying from 1 to 4 and changing it each time I add a
new variable. Is it possible to specify in the query the number of decimal
places that will be output rather than the number of decimal places for a
field and if so how?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...fbf2195073&dg=microsoft.public.access.queries

 

[John Spencer MVP]

You can use the round function to round to a specific number of decimal places.

So in a query you might have a calculated field

Field: MyField: Round([MyTable].[MyField],2)

Or in SQL

SELECT Round([MyTable].[MyField],2) as MyField
FROM MyTable

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[LynMVM]

Dear John,
Thank you for your answer. I am sure it should work it must just be
something I am doing wrong. I am only a rather basic user of Access.
It just keeps saying there is a compilation error and I should view the code.
I cannot seem to find the code and probably would not understand what was
the matter even if I did. I just tried it out on one of my calculated
variables.

I tried both rAGE: Round([NewCalculatedVariables]![AGE] ,1)
working on the variable with its 13 dec places and

AGE: Round (( [TblSubject data]![DateOfVisit] - [TblSubject
data]![DateOfBirth])/365.25) , 1)


working from the original variables on the AGE calculation.
I have tried less brackets more brackets more or less spaces! But it keeps
on saying there is a compliation error

Thank you

You can use the round function to round to a specific number of decimal places.

So in a query you might have a calculated field

Field: MyField: Round([MyTable].[MyField],2)

Or in SQL

SELECT Round([MyTable].[MyField],2) as MyField
FROM MyTable

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have several databases where we measure and store various variables but
from these calculate others. For example we input date of birth and date of
questionnaire and then work with age at time of data collection. As there are
a large number of these variables and quite a few similar databases each time
I need to update the calculated variables using my queries they calculate the
results to 13 decimal places. I have specified the number of decimal places
in the resulting table and theat works OK but whenever I add a new variable
which I do frequently it deletes the original table and so reverts to 13
decimal places. I am bored of looking up the number of decimal places each
variable needs to have varying from 1 to 4 and changing it each time I add a
new variable. Is it possible to specify in the query the number of decimal
places that will be output rather than the number of decimal places for a
field and if so how?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...fbf2195073&dg=microsoft.public.access.queries

 

[John Spencer MVP]

What you have posted, LOOKS correct, although I would use a period (.)to
separate the table and field names instead of the exclamation (!).

First, which version of Access are you using (including Service Pack level)?

Second, post the entire SQL (View: SQL) statement of the query that is failing.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[LynMVM]

Dear John,
I am using Access 2003 (11.6566.8221) SP2.
It will not allow me to switch to SQL view it just comes up with the message
"There was an error compiling tis function.
The Visual Basic module contains a syntax error.
Check the code, and then recompile it. OK"
I tiried a . intead of the ! and it did not make any difference
I am using the Round funciton in a MakeTable Query with
The following in my field box:
rAGE: Round ( [NewCalculatedVariables]![AGE] , 1)
Where AGE is my field in the Table NewCalculatedVariables that has 13
decimal places.
To check there was no problem in the data I tried using the field with other
functions, e.g.
2xAGE: [NewCalculatedVariables]![AGE]*2
which works fine (to double the Age (still with 13 dec places) so I do not
think there is a problem with the source field.

Thank you with your help on this because this is something I would use a lot
if I could get it working. I have been telling my nurses not to calculate any
fields at the data input stage (because they get it wrong too often) and
that we will calculate all the derived variables but when you get a lot of
feilds (10 or more) these 13 dec.pl. start to become a nuisance.
Thank you
Lynn

What you have posted, LOOKS correct, although I would use a period (.)to
separate the table and field names instead of the exclamation (!).

First, which version of Access are you using (including Service Pack level)?

Second, post the entire SQL (View: SQL) statement of the query that is failing.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dear John,
Thank you for your answer. I am sure it should work it must just be
something I am doing wrong. I am only a rather basic user of Access.
It just keeps saying there is a compilation error and I should view the code.
I cannot seem to find the code and probably would not understand what was
the matter even if I did. I just tried it out on one of my calculated
variables.

I tried both rAGE: Round([NewCalculatedVariables]![AGE] ,1)
working on the variable with its 13 dec places and

AGE: Round (( [TblSubject data]![DateOfVisit] - [TblSubject
data]![DateOfBirth])/365.25) , 1)


working from the original variables on the AGE calculation.
I have tried less brackets more brackets more or less spaces! But it keeps
on saying there is a compliation error

Thank you
"John Spencer MVP" wrote:

 

[John Spencer MVP]

Do you have a VBA function called ROUND that you have built?

Have you opened up a VBA window and compiled the code and fixed any errors
that occur?

Even if you have a custom function, changing the view of the query should not
cause a problem.

It is possible that you are suffering some corruption in your application. If
so try Allen Browne's article on Recovering from Corruption at:
http://allenbrowne.com/ser-47.html

Also you take a look at Tony Toews' site
http://www.granite.ab.ca/access/corruptmdbs.htm

Jerry Whittle, Microsoft Access MVP has a white paper in a Word document named
Fix Corrupt Access Database towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[LynMVM]

Dear John,
When your 1st suggestion appeared to be what I had already tried (and failed
to get to work) I feared I had not explained myself well and was going to
fail due to my inability to talk Access speak BUT

You are an Access genius!

I have just tried the Round function on another of my databases (almost
identical but a different author and dataset )and it works fine.
I have not yet cured the corruption on that database but I would have spent
several more days looking up what I was doing wrong with a rather basic
funtion and it was the corrupt database all along.
Thank you

 

[LynMVM]

Dear John,
I imported all the tables, queries and forms into a new database and now the
round function works just as I thought in the first place it should!
I would never have guessed that corruption could cause that.
Thank you

Do you have a VBA function called ROUND that you have built?

Have you opened up a VBA window and compiled the code and fixed any errors
that occur?

Even if you have a custom function, changing the view of the query should not
cause a problem.

It is possible that you are suffering some corruption in your application. If
so try Allen Browne's article on Recovering from Corruption at:
http://allenbrowne.com/ser-47.html

Also you take a look at Tony Toews' site
http://www.granite.ab.ca/access/corruptmdbs.htm

Jerry Whittle, Microsoft Access MVP has a white paper in a Word document named
Fix Corrupt Access Database towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dear John,
I am using Access 2003 (11.6566.8221) SP2.
It will not allow me to switch to SQL view it just comes up with the message
"There was an error compiling tis function.
The Visual Basic module contains a syntax error.
Check the code, and then recompile it. OK"
I tiried a . intead of the ! and it did not make any difference
I am using the Round funciton in a MakeTable Query with
The following in my field box:
rAGE: Round ( [NewCalculatedVariables]![AGE] , 1)
Where AGE is my field in the Table NewCalculatedVariables that has 13
decimal places.
To check there was no problem in the data I tried using the field with other
functions, e.g.
2xAGE: [NewCalculatedVariables]![AGE]*2
which works fine (to double the Age (still with 13 dec places) so I do not
think there is a problem with the source field.

 

[John Spencer MVP]

Not a genius, just someone who has run into the problem and spent quite a bit
of time trying to figure out what I was doing wrong. Hard lessons last a long
time.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County