Rabu, 29 April 2015

RE: [MS_AccessPros] Re: Divide by Zero

 

Doyce,
 
If [TotalProduced] is actually text, I would consider that a problem. Some database systems would have an issue with using a string in a numeric operation ([CountOfUnitID]/[TotalProduced]). Access is helpful in that: 123/"3" = 41.
 
I would make sure [TotalProduced] is numeric if at all possible. If the field will never be Null, you could use:

 Percent: IIf(Val([TotalProduced])=0, 0, Round(([CountOfUnitID]/Val([TotalProduced]))*100,1))
 
I prefer to be more explicit in my expressions.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 06:26:23 -0700
Subject: [MS_AccessPros] Re: Divide by Zero



Hey, I just figured this out. Total Produced is returning the zero as text instead of a number. If I use this formula, I get the results I want:
Percent: IIf([TotalProduced]="0",0,Round(([CountOfUnitID]/[TotalProduced])*100,1))
 
Problem Solved.
Doyce

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

 Hello Friends,
I have a query where I'm trying to caculate a percentage based on two other fields. This formula works unless there is a zero in the Total Produced field and then it returns #Num!. Here is the formula:
Percent: IIf([TotalProduced] Is Null,0,Round(([CountOfUnitID]/[TotalProduced])*100,1))
Here is the SQL of the query:
SELECT qrySubPercentageTrailersNotTaggedWhenProduced.Type, qrySubPercentageTrailersNotTaggedWhenProduced.CountOfUNITID, qrySubPercentageTrailersNotTaggedWhenProduced.TotalProduced, IIf([TotalProduced] Is Null,0,Round(([CountOfUnitID]/[TotalProduced])*100,1)) AS [Percent]
FROM qrySubPercentageTrailersNotTaggedWhenProduced;
I appreciate someone's help with my syntax.
Doyce


__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)

.

__,_._,___

Tidak ada komentar:

Posting Komentar