S
Souris
I have following union query and WorkingHour is single type field.
It seems that the SUM function returns floating point value and retunrs
WorkingHour 2.29999995231628 instead of 2.3 in the table.
2.3 is physical data in the table.
Are there any workaround to return data from table like 2.3 but not
2.29999995231628?
It works if the data is 2.5.
The query return correct value without meeting count.
If I have 2 seperate query than both are working fine.
Your help is great appreciated,
SELECT 15 as MyID, ActivityDate , tblEmployee.FirstName as Employee ,
SUM(IIF (tblActivityDetails.Meetingtype = 21 and
DateValue(tblActivityDetails.meetingdate) between
cdate([forms]![frmReport]![dtpStartdate]) and
cdate([forms]![frmReport]![dtpEnddate]) and
tblActivityDetails.Status = 3,1,0)) as MyValue , "meeting count" as MyCode
from tblActivityDetails
INNER JOIN tblEmployee on tblActivityDetails.Employee_ID =
tblEmployee.Employee_ID
WHERE DateValue( tblActivityDetails.MeetingDate) between
cdate([forms]![frmReport]![dtpStartdate]) and
cdate([forms]![frmReport]![dtpEnddate])
GROUP BY ActivityDate, tblEmployee.FirstName
UNION
SELECT 6 as MyID, ActivityDate, tblEmployee.FirstName as Employee,
WorkingHour as MyValue, "Working Hour" as MyCode from tblActivity
INNER JOIN tblEmployee on tblEDAActivity.Employee_ID = tblEmployee.Employee_ID
WHERE ActivityDate between cdate([forms]![frmReport]![dtpStartdate])-1 and
cdate( [forms]![frmReport]![dtpEnddate]);
It seems that the SUM function returns floating point value and retunrs
WorkingHour 2.29999995231628 instead of 2.3 in the table.
2.3 is physical data in the table.
Are there any workaround to return data from table like 2.3 but not
2.29999995231628?
It works if the data is 2.5.
The query return correct value without meeting count.
If I have 2 seperate query than both are working fine.
Your help is great appreciated,
SELECT 15 as MyID, ActivityDate , tblEmployee.FirstName as Employee ,
SUM(IIF (tblActivityDetails.Meetingtype = 21 and
DateValue(tblActivityDetails.meetingdate) between
cdate([forms]![frmReport]![dtpStartdate]) and
cdate([forms]![frmReport]![dtpEnddate]) and
tblActivityDetails.Status = 3,1,0)) as MyValue , "meeting count" as MyCode
from tblActivityDetails
INNER JOIN tblEmployee on tblActivityDetails.Employee_ID =
tblEmployee.Employee_ID
WHERE DateValue( tblActivityDetails.MeetingDate) between
cdate([forms]![frmReport]![dtpStartdate]) and
cdate([forms]![frmReport]![dtpEnddate])
GROUP BY ActivityDate, tblEmployee.FirstName
UNION
SELECT 6 as MyID, ActivityDate, tblEmployee.FirstName as Employee,
WorkingHour as MyValue, "Working Hour" as MyCode from tblActivity
INNER JOIN tblEmployee on tblEDAActivity.Employee_ID = tblEmployee.Employee_ID
WHERE ActivityDate between cdate([forms]![frmReport]![dtpStartdate])-1 and
cdate( [forms]![frmReport]![dtpEnddate]);