Doyce - I see what you mean. The query is returning the DCount as a string. Wrapping it in CLng() should fix that.
---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@roadsysinc.com> wrote :
Bill,
Here is the SQL of the query:
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DCount("UnitID","New Primary","[PickupDate] Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]") AS TotalProduced
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE ((([New Primary].PICKUPDATE) Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date] And ([New Primary].PICKUPDATE)<>[date tag]) AND ((TAG.TagException)=False)) OR ((([New Primary].PICKUPDATE) Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]) AND ((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null));
Doyce
---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
? 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
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 07:41:51 -0700
Subject: RE: [MS_AccessPros] Re: Divide by Zero
Duane,
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
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
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 06:26:23 -0700
Subject: [MS_AccessPros] Re: Divide by Zero
---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :
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: wrmosca@comcast.net
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
Tidak ada komentar:
Posting Komentar