Rabu, 29 April 2015

RE: [MS_AccessPros] Re: Divide by Zero

 

Doyce - I see what you mean. The query is returning the DCount as a string. Wrapping it in CLng() should fix that.


Mighty strange. 

-Bill


---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 :

Doyce - Something is going on here. DCount returns a variant Long. Being a variant, it can return a Null as well, which happens if the field in the criteria argument is not in the domain (ie the table or query). It might look like a string in the Immediate window , but if you run this:
?vartype(dcount("*", "Customers"))
you will get 3 which is a Long.


Here is a full list of vartypes:
vbVartypes
Value     Variant type
0     Empty (unitialized)
1     Null (no valid data)
2     Integer
3     Long Integer
4     Single
5     Double
6     Currency
7     Date
8     String
9     Object
10     Error value
11     Boolean
12     Variant (only used with arrays of variants)
13     Data access object
14     Decimal value
17     Byte
36     User Defined Type
8192           Array

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com



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

It is left aligned.
Doyce

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

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: 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