I would then wrap the expression in Val()
TotalProduced: Val(DCount("UnitID","New Primary","[PickupDate] Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]"))
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 07:49:48 -0700
Subject: RE: [MS_AccessPros] Re: Divide by Zero
It is left aligned.
TotalProduced: Val(DCount("UnitID","New Primary","[PickupDate] Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]"))
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 07:49:48 -0700
Subject: RE: [MS_AccessPros] Re: Divide by Zero
It is left aligned.
Doyce
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
I would also expect it to return a number. When I enter this into the debug window:
? DCount("*","tblTest")
The result is returned with a leading space which suggests a number. When you viewe the query in datasheet, is the TotalProduced column left or right-aligned?
Duane Hookom, MVP
MS Access
? DCount("*","tblTest")
The result is returned with a leading space which suggests a number. When you viewe the query in datasheet, is the TotalProduced column left or right-aligned?
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 07:41:51 -0700
Subject: RE: [MS_AccessPros] Re: Divide by Zero
Duane,
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 07:41:51 -0700
Subject: RE: [MS_AccessPros] Re: Divide by Zero
Duane,
TotalProduced is actually the result of a Dcount formula in a query with date parameters.
TotalProduced: DCount("UnitID","New Primary","[PickupDate] Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]")
I wasn't expecting Dcount to return the number in text format. Does Dcount always do that or would it have something to do with the query being a totals query?
Doyce
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
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
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
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 :
---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
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 (7) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar