Where/how to set rounding property for a Query field

[Guest]

I'm very new to Access and would appreciate if someone would tell me exactly
where (and how, if possible) within the Query Design View I would set the
query field properties for a rounding problem.

I've set up an averaging query calculation that is rounding to 2 decimals,
but when I click anywhere else within the query results, the answer changes
to 3 decimal places, which is the correct result. However, since it will not
be used for actual accounting purposes, I would like it to round to 2
decimals and format as currency for my report purposes. I should say that my
underlying table field is set to Currency data type and Currency/Auto in the
Field Properties. I tried changing this to 2 decimal places, with no
success.

Thanks for your help

 

[Jim]

I'm no expert so listen to the others.

By Query Design View you mean the Query by Example in Access then my
suggestion would be to add the round option in the field.
Instead of Avg: [total]/[qty] use Avg: Round([total]/[qty],2)
If you want the query to display two decimal points surround the calculation
with a Format function.

In SQL view the Field would be: Round([total]/[qty],2) AS Avg

Hope that helps.

 

[Guest]

Jim
I think your answer is probably what I'm looking for, but my knowledge is
too limited to understand how to set it up. I'm not even sure what the SQL
view is-- that's how new I am.

When I'm setting up the query, I click the Total button on the toolbar and
then choose Avg, which gives me the correct answer when I run it, but won't
stay formatted at 2 decimals if I click on any other part of the query.

Apparently, I should be setting this up in some other way (area)??

Thanks for taking time to help out.
--
smither fan

I'm no expert so listen to the others.

By Query Design View you mean the Query by Example in Access then my
suggestion would be to add the round option in the field.
Instead of Avg: [total]/[qty] use Avg: Round([total]/[qty],2)
If you want the query to display two decimal points surround the calculation
with a Format function.

In SQL view the Field would be: Round([total]/[qty],2) AS Avg

Hope that helps.

I'm very new to Access and would appreciate if someone would tell me
exactly
where (and how, if possible) within the Query Design View I would set the
query field properties for a rounding problem.

I've set up an averaging query calculation that is rounding to 2 decimals,
but when I click anywhere else within the query results, the answer
changes
to 3 decimal places, which is the correct result. However, since it will
not
be used for actual accounting purposes, I would like it to round to 2
decimals and format as currency for my report purposes. I should say that
my
underlying table field is set to Currency data type and Currency/Auto in
the
Field Properties. I tried changing this to 2 decimal places, with no
success.

Thanks for your help

 

[Guest]

Hold the press--I figured out where to put it. I pasted your
Round([total]/[qty],2) AS Avg into the Input Mask of the Field Properties box
and it worked! Sorry for shouting but I have been f r u s t r a t e d.

Thanks so much for the info
--
smither fan

I'm no expert so listen to the others.

By Query Design View you mean the Query by Example in Access then my
suggestion would be to add the round option in the field.
Instead of Avg: [total]/[qty] use Avg: Round([total]/[qty],2)
If you want the query to display two decimal points surround the calculation
with a Format function.

In SQL view the Field would be: Round([total]/[qty],2) AS Avg

Hope that helps.

I'm very new to Access and would appreciate if someone would tell me
exactly
where (and how, if possible) within the Query Design View I would set the
query field properties for a rounding problem.

I've set up an averaging query calculation that is rounding to 2 decimals,
but when I click anywhere else within the query results, the answer
changes
to 3 decimal places, which is the correct result. However, since it will
not
be used for actual accounting purposes, I would like it to round to 2
decimals and format as currency for my report purposes. I should say that
my
underlying table field is set to Currency data type and Currency/Auto in
the
Field Properties. I tried changing this to 2 decimal places, with no
success.

Thanks for your help

 

[Van T. Dinh]

A simpler way if you simply want to display 2 decimal places in the
DatasheetView of the Query is to set the Format Property for the Column (in
the Query) to "Currency" or "0.00".

--
HTH
Van T. Dinh
MVP (Access)

Hold the press--I figured out where to put it. I pasted your
Round([total]/[qty],2) AS Avg into the Input Mask of the Field Properties
box
and it worked! Sorry for shouting but I have been f r u s t r a t e d.

Thanks so much for the info
--
smither fan

I'm no expert so listen to the others.

By Query Design View you mean the Query by Example in Access then my
suggestion would be to add the round option in the field.
Instead of Avg: [total]/[qty] use Avg: Round([total]/[qty],2)
If you want the query to display two decimal points surround the
calculation
with a Format function.

In SQL view the Field would be: Round([total]/[qty],2) AS Avg

Hope that helps.

I'm very new to Access and would appreciate if someone would tell me
exactly
where (and how, if possible) within the Query Design View I would set
the
query field properties for a rounding problem.

I've set up an averaging query calculation that is rounding to 2
decimals,
but when I click anywhere else within the query results, the answer
changes
to 3 decimal places, which is the correct result. However, since it
will
not
be used for actual accounting purposes, I would like it to round to 2
decimals and format as currency for my report purposes. I should say
that
my
underlying table field is set to Currency data type and Currency/Auto
in
the
Field Properties. I tried changing this to 2 decimal places, with no
success.

Thanks for your help

 

[Guest]

Hi Van

I tried your work-around, but it wouldn't keep the $ and 2 decimals only.
In Datasheet View, it looked fine until I clicked within the area, then it
changed back to 3 decimals. This is actually the way I had originally set it
up.

So far, Jim's suggestion is working great. Hopefully, it stays that way. I
just had no idea how to set it up:

(Format>Decimal), (the Input Mask>manually typeRound([total]/[qty],2).

Thanks anyway for taking your time.
--
smither fan

A simpler way if you simply want to display 2 decimal places in the
DatasheetView of the Query is to set the Format Property for the Column (in
the Query) to "Currency" or "0.00".

--
HTH
Van T. Dinh
MVP (Access)

Hold the press--I figured out where to put it. I pasted your
Round([total]/[qty],2) AS Avg into the Input Mask of the Field Properties
box
and it worked! Sorry for shouting but I have been f r u s t r a t e d.

Thanks so much for the info
--
smither fan

I'm no expert so listen to the others.

By Query Design View you mean the Query by Example in Access then my
suggestion would be to add the round option in the field.
Instead of Avg: [total]/[qty] use Avg: Round([total]/[qty],2)
If you want the query to display two decimal points surround the
calculation
with a Format function.

In SQL view the Field would be: Round([total]/[qty],2) AS Avg

Hope that helps.


I'm very new to Access and would appreciate if someone would tell me
exactly
where (and how, if possible) within the Query Design View I would set
the
query field properties for a rounding problem.

I've set up an averaging query calculation that is rounding to 2
decimals,
but when I click anywhere else within the query results, the answer
changes
to 3 decimal places, which is the correct result. However, since it
will
not
be used for actual accounting purposes, I would like it to round to 2
decimals and format as currency for my report purposes. I should say
that
my
underlying table field is set to Currency data type and Currency/Auto
in
the
Field Properties. I tried changing this to 2 decimal places, with no
success.

Thanks for your help

 

[Van T. Dinh]

Yes, the difference is that Jim's method using the Format function returns a
String which won't change when the Focus is on the value. The Fomat
property leave the value as numeric so when it has the focus, it will
display the *actual* value.

 

[Guest]

Hi Van

I meant to say the Format is set to Currency. I said Decimal by mistake.
Anyway, it's all working great now. I get a second to rest before the next
stressful Access moment!

Thanks
--
smither fan

Yes, the difference is that Jim's method using the Format function returns a
String which won't change when the Focus is on the value. The Fomat
property leave the value as numeric so when it has the focus, it will
display the *actual* value.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van

I tried your work-around, but it wouldn't keep the $ and 2 decimals only.
In Datasheet View, it looked fine until I clicked within the area, then it
changed back to 3 decimals. This is actually the way I had originally set
it
up.

So far, Jim's suggestion is working great. Hopefully, it stays that way.
I
just had no idea how to set it up:

(Format>Decimal), (the Input Mask>manually typeRound([total]/[qty],2).

Thanks anyway for taking your time.