Format problem in a form/report

[Haim Beyhan]

I'm using Access XP with SQL Server database.
I have a view with a sum query.
select colA, sum(colB), sum(colC) group by colA
colB is integer and colC is decimal(7,3)
I created a form/report and used this view as the recordsource. I changed
the format of colB to standard with 2 decimal places and colC to standard
with 3 decimal places. Although I see the colB as expected, I don't see colC
as standard format.
eg. I see 1234.567 instead of 1,234.567

I have other views also that have sum columns and the problem is the same in
the reports also.

I think the problem is only with decimal data type and only when I sum the
values. If I use instead float, real, money I don't have this problem. But I
need to use decimal.

Your help will be much appreciated.

Thanks in advance,

Haim Beyhan
haimb@remove_enigma.com (remove remove_ in order to send mail directly)

 

[Yuan Shao]

Hi Haim,

Thanks for your post. According to your description, I understand that when
you create a text box on a form that references a numeric data element from
a SQL view and tries to set the properties of the text box to specify
formatting for that value, the formatting is ignored and displays the
number as stored in the view or SQL Server table. The version of Access you
used is Access 2002. If I have misunderstood, please feel free to let me
know.

Based on my research, this is a known issue for Access 2002. It seems that
the only suggested workaround is to put the calculations in the form
instead of the view. Currently, our developer group is investigating this
issue. If you have any concerns, please feel free to post in the group.

Best regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Haim Beyhan]

Thanks for the reply.

Haim