Re: union query precision issue

One I am suspicious of the criteria you uave imposed in the calculation

You have Status = 3,1,0 that should probably be
Round
(SUM
(IIF (tblActivityMeetingDetails.Meetingtype = 30 and
DateValue(tblActivityMeetingDetails.meetingdate) between
cdate([forms]![frmReport]![dtpStartdate]) and
cdate([forms]![frmReport]![dtpEnddate]) and
tblActivityMeetingDetails.Status IN (3,1,0))),2,0) as MyValue

You could try forcing the data type to CCur if you are interested in accuracy up to 4 decimal places and no more

CCUR
(SUM
(IIF (tblActivityMeetingDetails.Meetingtype = 30 and
DateValue(tblActivityMeetingDetails.meetingdate) between
cdate([forms]![frmReport]![dtpStartdate]) and
cdate([forms]![frmReport]![dtpEnddate]) and
tblActivityMeetingDetails.Status IN (3,1,0))),2,0) as MyValue

You might notice that I forced a Zero value to be returned as the third argument of the IIF expression.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Souris wrote:
SELECT 15 as MyID, ActivityDate , tblEmployee.Abbreviation as employee ,
round(SUM(IIF (tblActivityMeetingDetails.Meetingtype = 30 and DateValue(tblActivityMeetingDetails.meetingdate) between cdate([forms]![frmReport]![dtpStartdate]) and cdate([forms]![frmReport]![dtpEnddate]) and tblActivityMeetingDetails.Status = 3,1,0)),2) as MyValue , "Special" as MyCode
from tblActivityMeetingDetails
INNER JOIN tblEmployee on tblActivityMeetingDetails.Employee_ID = tblEmployee.Employee_ID
WHERE DateValue( tblActivityMeetingDetails.MeetingDate) between cdate([forms]![frmReport]![dtpStartdate]) and cdate([forms]![frmReport]![dtpEnddate])
GROUP BY ActivityDate, tblEmployee.Abbreviation

Hi, It me again. I found the problem.
If It works if I remove above union query from my union query.

It looks like the SUM function screw the union query.
If it is are there any workaround?
May I have SUM function in the union query to keep the decimal precisions?

Your help is great appreciated,




"Jerry Whittle" wrote:

Please provide the SQL statement for the union query.
--
Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Souris" wrote:

I have a data 2.3 in a single data type field.

I get the return 2.3 using select query, but it returns 2.29999995231628 when I use union query even I use round(myfield,2)


I am not sure why the union query return the value.
I physically enter 2.3 value. The table and select query show the right value.

I tried to use 2.5 then the union query returns good value.

How is it possible?

Are there any workaround?

Thanks millions,


.

Loading