ACCESS FEED: RE: [MS_AccessPros] Re: Divide by Zero

Rabu, 29 April 2015

RE: [MS_AccessPros] Re: Divide by Zero

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 (12)

Visit Your Group