Problem in (1-0)/<variable>equation

  • Thread starter Thread starter Darren \(at work\)
  • Start date Start date
D

Darren \(at work\)

Hi,
I have a query that basically says:

AttritionRate: ([Prodfaults]-[ProdFaultsMinus])/[Enter Input]

If:

'ProdFaults = 1'
'ProdFaultsminus = 0'
'EnterInput = 500'

The answer should be: 0.002

But what returns is actually: 0

Anyone any idea why this happens and how I can get this query to return the
correct value if and when:
'ProdFaultsMinus = 0'

Many thanks for any and all help.

Darren
 
"Kelvin" very kindly wrote in reply to my message

| Try
|
| AttritionRate: CDbl(([Prodfaults]-[ProdFaultsMinus])/[Enter Input])
|
....
Thanks for the reply Kevin, but unfortunately that didn't work. It still
returns a value of zero.

If it helps:

'Prodfaults': is the result of a query
'ProfaultsMinus'; is the result of another query
'[Enter Input]': is a user entered value

If I run Prodfaults, this returns a value of '1'.
If I run 'ProdFaultsMinus', this returns a value of zero.

The 'AttritionRate' is then used within another query which is filtered by a
field 'PartCode'.

If 'ProdfaultsMinus' is a value other than '0', this works fine.

Darren
 
AHA! Is ProdFaultsMinus returning Zero or is it returning NULL? If it is
returning NULL then your entire expression becomes Null.

TRY using the Nz function (Null to Zero) and see if that takes care of the
problem.

AttritionRate: CDbl(([Prodfaults]- NZ([ProdFaultsMinus],0))/[Enter Input])
 
Try to format the query field AttritionRate. In query
design window, click on the filed AttritionRate. Then
call Properties window and in Format set some suitable
formatting (specify 6-8 decimals).

Or try this:
AttritionRate: ([Prodfaults]-[ProdFaultsMinus])/[Enter
Input]*1.2/1.2

If all variables (Prodfaults,ProdFaultsMinus,Enter Input)
are integer numbers, Access may think that the result
should be integer, so 0.002 becomes 0. By adding *1.2/1.2
you are introducing non integer component and the result
may be OK.

:-)
 
Thanks John & Dejan for the replies. The code is at work, so it will be
Monday morning before I get a chance to try your answers. I will post here
with the outcome.

Many thanks. The help is, as always, most appreciated.

Darren
 
Dejan said:
Try to format the query field AttritionRate. In query
design window, click on the filed AttritionRate. Then
call Properties window and in Format set some suitable
formatting (specify 6-8 decimals).

Or try this:
AttritionRate: ([Prodfaults]-[ProdFaultsMinus])/[Enter
Input]*1.2/1.2
Click to expand...
You might also need to put the terms that aren't integers before the
calculation
that is causing trouble, not after:

([Prodfaults]-[[ProdFaultsMinus])*1.0000/1.0000/[Enter Input]

SK
If all variables (Prodfaults,ProdFaultsMinus,Enter Input)
are integer numbers, Access may think that the result
should be integer, so 0.002 becomes 0. By adding *1.2/1.2
you are introducing non integer component and the result
may be OK.

:-)


-----Original Message-----
Hi,
I have a query that basically says:

AttritionRate: ([Prodfaults]-[ProdFaultsMinus])/[Enter

Input]


If:

'ProdFaults = 1'
'ProdFaultsminus = 0'
'EnterInput = 500'

The answer should be: 0.002

But what returns is actually: 0

Anyone any idea why this happens and how I can get this
Click to expand...
query to return the

correct value if and when:
'ProdFaultsMinus = 0'

Many thanks for any and all help.

Darren

--
******************************
lose YOURSELF from email to reply. *
******************************


.
Click to expand...
Click to expand...
 
Thanks to all who have replied to my problem.

I have tried all the suggestions with no success.

This may help:
The field [ProdFaultsMinus] is the result of a query.
The query is:
----------------------------
SELECT Count(*) AS ProdfaultsMinus
FROM Inspection
WHERE (((Inspection.Partcode)=[Enter PartCode]) AND
((Inspection.InspectDate) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True))
GROUP BY Inspection.QA_Result
HAVING (((Inspection.QA_Result)="Pass" Or
(Inspection.QA_Result)="Retouched"));
-----------------------------

When ProdFaultsMinus in this query has no results, there is nothing returned
in the query results window (meaning that only the field name
[ProdFaultsMinus] is returned.). I would have thought that there should have
been the number '0'.

I have tried to format this as '0;0;0;0'
I have also tried;

1) IIF([Count(*)=0, 0, Count(*))
2) IIF([Count(*)="0", 0, Count(*))
3) IIF(IsNull(Count(*), 0, Count(*))

The above query returns the correct value if it is > 0.

Any ideas anyone...
Regards
Darren
 
| Thanks to all who have replied to my problem.
|
| I have tried all the suggestions with no success.
|
| This may help:
| The field [ProdFaultsMinus] is the result of a query.
| The query is:
| ----------------------------
| SELECT Count(*) AS ProdfaultsMinus
| FROM Inspection
| WHERE (((Inspection.Partcode)=[Enter PartCode]) AND
| ((Inspection.InspectDate) Between [Enter first date] And [Enter second
| date]) AND ((Inspection.Prod_Rej)=True))
| GROUP BY Inspection.QA_Result
| HAVING (((Inspection.QA_Result)="Pass" Or
| (Inspection.QA_Result)="Retouched"));
| -----------------------------

I tried replacing the GROUP BY and HAVING clauses with this:
---------------------------------------
SELECT Count(*) AS ProdfaultsMinus
FROM Inspection
WHERE (((Inspection.QA_Result)="Pass" Or (Inspection.QA_Result)="Retouched")
AND ((Inspection.Partcode)=[Enter PartCode]) AND ((Inspection.InspectDate)
Between [Enter first date] And [Enter second date]) AND
((Inspection.Prod_Rej)=True));
---------------------------------------
Whaddya know, it works. I can't for the life of me figure out why. Maybe
someone could shed some light on that for me?? :-)

Many thanks to those who took the time to answer my questions.

Best Regards
Darren
 
Back
Top