RE: [MS_AccessPros] Re: Divide by Zero

 

Got it! Thanks Bill!

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

Doyce - an int ranges from -32,768 to 32,767;  a long is from -2,147,483,648 to 2,147,483,647. I always use Long because with the databases I support 32,767 records is not always a safe ceiling.

-Bill

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

Bill,

I used Cint. What is the difference between CLong and Cint?
Doyce

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

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@...> 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: winberry.doyce@roadsysinc.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (16)

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___