union query precision issue

S

Souris

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,
 
S

Souris

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,
 
S

Souris

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,
 
J

John Spencer

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.
Click to expand...
Click to expand...
 
S

Souris

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,


John Spencer said:
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,
Click to expand...
Click to expand...
Click to expand...
 
S

Souris

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


John Spencer said:
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,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query 2
Union Query and Field Alias 7
Union Query 4
Comlex union query question 4
Union Query 0
Union Query to list duplicates 2
Union query not displaying select statement 1
How to assign table name to be value of a field in a UNION query? 2

Top