union query precision issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Souris - 16 Apr 2008 20:39 GMT

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,

Reply to this Message

Jerry Whittle - 16 Apr 2008 21:00 GMT

Please provide the SQL statement for the union query.

Signature

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.
>
[quoted text clipped - 11 lines]
>
> Thanks millions,

Reply to this Message

Souris - 17 Apr 2008 13:10 GMT

Thanks for the message,

My field type is Single, decimal is auto and no format in table design.

My single query is like following:

SELECT 6 as MyID, ActivityDate, tblEmployee.Abbreviation as Employee,
QualityHour as MyValue, "Quality Hour" as MyCode from tblActivity
INNER JOIN tblEmployee on tblActivity.EDA_ID = tblEmployee.Employee_ID
WHERE ActivityDate between cdate([forms]![frmReport]![dtpStartdate])-1 and
cdate( [forms]![frmReport]![dtpEnddate])

which returns 2.3 as database.

My union query is same just union other elements

SELECT 4 as MyID, ActivityDate, tblEmployee.Abbreviation as Employee,
MeetingHour as MyValue, "Meeting Hour" as MyCode from tblActivity
INNER JOIN tblEmployee on tblActivity.EDA_ID = tblEmployee.Employee_ID
WHERE ActivityDate between cdate([forms]![frmReport]![dtpStartdate])-1 and
cdate( [forms]![frmReport]![dtpEnddate])

SELECT 5 as MyID, ActivityDate, tblEmployee.Abbreviation as Employee,
QualityMinute as MyValue, "Quality Minute" as MyCode from tblActivity
INNER JOIN tblEmployee on tblActivity.EDA_ID = tblEmployee.Employee_ID
WHERE ActivityDate between cdate([forms]![frmReport]![dtpStartdate])-1 and
cdate( [forms]![frmReport]![dtpEnddate])

UNION

SELECT 6 as MyID, ActivityDate, tblEmployee.Abbreviation as Employee,
QualityHour as MyValue, "Quality Hour" as MyCode from tblActivity
INNER JOIN tblEmployee on tblActivity.EDA_ID = tblEmployee.Employee_ID
WHERE ActivityDate between cdate([forms]![frmReport]![dtpStartdate])-1 and
cdate( [forms]![frmReport]![dtpEnddate])

Above union query returns 2.29999995231628,but it works if data were 2.5.

Your help is great appreciated,

> Please provide the SQL statement for the union query.
>
[quoted text clipped - 13 lines]
> >
> > Thanks millions,

Reply to this Message

Souris - 17 Apr 2008 13:25 GMT

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,

> Please provide the SQL statement for the union query.
>
[quoted text clipped - 13 lines]
> >
> > Thanks millions,

Reply to this Message

John Spencer - 17 Apr 2008 14:42 GMT

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
[quoted text clipped - 37 lines]
>>>
>>> Thanks millions,

Reply to this Message

Souris - 17 Apr 2008 15:27 GMT

Thanks for the message,
I only looking for Status = 3 and 1 and zero are the value returns depends
on IIF is true or false.

It still returns floating point value.

Thanks again,

> One I am suspicious of the criteria you uave imposed in the calculation
>
[quoted text clipped - 69 lines]
> >>>
> >>> Thanks millions,

Reply to this Message

Souris - 17 Apr 2008 15:31 GMT

interesting thing is the value retruns correct for itself, but affect other
no sum query.

> One I am suspicious of the criteria you uave imposed in the calculation
>
[quoted text clipped - 69 lines]
> >>>
> >>> Thanks millions,

Reply to this Message