UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need A 0 Instead Of #div/0!, Access 2016    
 
   
tmarie827
post Apr 17 2017, 04:19 PM
Post#1



Posts: 292
Joined: 5-August 09
From: Sin City


Hi,

I have a few text boxes on my report.

Txtbox 1 Control Source: =Count([qry_AverageTimeExpedited].[WRNumber]) Counts number of jobs that were completed in the defined range.
Txtbox 2 Control Source: =Sum([qry_AverageTimeExpedited].[WorkDays]) Sums all workdays for all jobs that was worked in the defined range.
Txtbox 3 Control Source: =[TotalDaysALLExpedited]\[TotalWRsALLExpedited] Average number of workdays.

This works fine, but if txtbox 1 is "0", then it will show #Div/0! on my report for txtbox 2 & 3. How can I show 0 instead?

Thanks!
Tina

--------------------
Tina
Go to the top of the page
 
GroverParkGeorge
post Apr 17 2017, 04:52 PM
Post#2


UA Admin
Posts: 29,127
Joined: 20-June 02
From: Newcastle, WA


You should be able to use NZ() here to trap the error, but there's more to it than that.

First, you refer to these as "TxtBox 1", "TxtBox 2", and "TxtBox 3", but then your actual control source for the third one is "[TotalDaysALLExpedited]\[TotalWRsALLExpedited]". I assume that these are the real names of the controls on the report?

And further, it appears that your problem is that the first textbox, which is called "TotalWRsALLExpedited]" I would guess, is the one where the count of jobs can be 0. That's where the divide by 0 error would be generated.

Am I on the right track with names and references?

It also looks like you're using division by \ not / ? Is that on purpose, or just a typo here?

In any event, the actual solution depends on what you want to have shown if there are no jobs completed in the ranged. If there are dozens of workdays for all jobs worked, is it correct to show an average for all of those jobs, completed or not, based only on those which are completed?

--------------------
Go to the top of the page
 
tmarie827
post Apr 17 2017, 05:11 PM
Post#3



Posts: 292
Joined: 5-August 09
From: Sin City


Hi-
First...you are correct the real names of the textboxes are the controls.
And further....yes, this is can be 0. Actually both will be 0 because if there were no WRs then there are no days.
It works with the \, I can change it to /.
I want it to show 0 for all 3 text boxes. The number of WRs show 0, so the rest should also show 0.

Thanks!
Tina

--------------------
Tina
Go to the top of the page
 
GroverParkGeorge
post Apr 17 2017, 05:41 PM
Post#4


UA Admin
Posts: 29,127
Joined: 20-June 02
From: Newcastle, WA


There is a difference in the two divisions. The "standard" one, denoted by the forward slash, returns the decimal value for non-prime division, whereas the other option, denoted by the backward slash, returns only the whole number part of the result.

I'm not sure what the proper term for this second division is.

Attached File  2017_04_17_15_36_38.png ( 25.57K )Number of downloads: 3


I'm going to suggest a conditional If for the second and third controls rather than NZ():

for control 2 : Iif(Count([qry_AverageTimeExpedited].[WRNumber]) =0, 0, Sum([qry_AverageTimeExpedited].[WorkDays]))

for control 3: Iif(Count([qry_AverageTimeExpedited].[WRNumber]) =0, 0, [TotalDaysALLExpedited]\[TotalWRsALLExpedited])

--------------------
Go to the top of the page
 
tmarie827
post Apr 17 2017, 05:54 PM
Post#5



Posts: 292
Joined: 5-August 09
From: Sin City


Thank you, I'll try it in the morning!
I think I want the backward slash because I don't want the decimal points.

Thanks!
Tina

--------------------
Tina
Go to the top of the page
 
tmarie827
post Apr 18 2017, 07:39 PM
Post#6



Posts: 292
Joined: 5-August 09
From: Sin City


Hi-
Is there an error in the syntax? I entered the syntax and keep getting a syntax error about a comma.
Thanks.
Tina

--------------------
Tina
Go to the top of the page
 
GroverParkGeorge
post Apr 18 2017, 08:05 PM
Post#7


UA Admin
Posts: 29,127
Joined: 20-June 02
From: Newcastle, WA


Show us the syntax used. Show us the exact text of the error.

--------------------
Go to the top of the page
 
tmarie827
post Apr 19 2017, 12:10 PM
Post#8



Posts: 292
Joined: 5-August 09
From: Sin City


Hi-
Control Source
[Iif=(Count([qry_AverageTimeExpedited].[WRNumber]) =0, 0, [TotalWorkDaysALLExpedited]\[TotalWRsALLExpedited])

The syntax error is attached.

Thanks!
Tina

Attached File(s)
Attached File  Capture.JPG ( 27.14K )Number of downloads: 6
 

--------------------
Tina
Go to the top of the page
 
mike60smart
post Apr 20 2017, 11:31 AM
Post#9


UtterAccess VIP
Posts: 11,981
Joined: 6-June 05
From: Dunbar,Scotland


Hi Tina

This :- [Iif=(Count([qry_AverageTimeExpedited].........

Should be :- [Iif(Count([qry_AverageTimeExpedited].......

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th April 2017 - 02:07 AM