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@roadsysinc.com> wrote :
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: wrmosca@comcast.net
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar