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
'====================================================
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 said:
Please provide the SQL statement for the union query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
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,