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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Iif, Isnull, Nz, #num!(error), Access 2016    
 
   
Scooter1
post Jan 29 2018, 11:23 PM
Post#1



Posts: 62
Joined: 8-January 18



Ok, I have most of my work completed, but one minor problem.



my report has the following unbound textboxes:

txt1 txt2 txt3 txt4
unbound unbound unbound unbound
name:totalRegLV totalSLV num1 amount

the calculation for txt1, txt2, is like this. IIF(IsNull([totalRegLV]), 0, [totalRegLV]). basically saying that if the value is null, then put zero in this field. There is no problem here. The problem is in the calculation of txt4.

Here is what I have. =IIF(IsNull([totalRegLV]), 0, [totalSLV]/([totalRegLV]*num1)). What I am evaluating here is if tx1, and txt2 both have zero value, then put zero in txt4. Else do the calculation. I am getting the false part ok, I'm just not getting the true part. I've tried using NZ before the IIF, and it did not change anything, so me thinking, maybe I am missing a IIF loop or the entire calculation is wrong.


Go to the top of the page
 
theDBguy
post Jan 30 2018, 01:29 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,929
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I’m a bit confused. Did you say the name of txt1 is totalRegLV?
Go to the top of the page
 
ScottGem
post Jan 30 2018, 07:52 AM
Post#3


UtterAccess VIP / UA Clown
Posts: 32,213
Joined: 21-January 04
From: LI, NY


What is the name of the text box? Are TotalRegLV and the others different controls on the report?

Also how are those values populated?
Go to the top of the page
 
BruceM
post Jan 30 2018, 08:33 AM
Post#4


UtterAccess VIP
Posts: 7,752
Joined: 24-May 10
From: Downeast Maine


QUOTE
What I am evaluating here is if tx1, and txt2 both have zero value

Just to be clear, null and zero are quite different. Null is, essentially, "unknown", while 0 is a known value.

I almost always do such calculation in the record source query rather than in text boxes. In any case, I believe the txt4 calculation will be based on the original values of txt1 and txt2, not the newly calculated value. Also, you will get an error if either totalRegLV or num1 is 0, since they work together to make the divisor, and division by zero is not possible.

If any of the values are Null, the return value will be Null, since unknown divided by or times or plus or minus anything is still unknown.

Clarification is needed.
Go to the top of the page
 
Scooter1
post Jan 30 2018, 10:04 AM
Post#5



Posts: 62
Joined: 8-January 18



Bruce,

Thanks, I can't provide the actual db, but I do have this little sample db that works the same thing.

You pointed out what I suspected in the beginning. Hope to get this straighten out, this is the last piece of the puzzle.
Attached File(s)
Attached File  TechSupport_3_Rev3.zip ( 111.7K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Jan 30 2018, 11:41 AM
Post#6


UtterAccess VIP
Posts: 9,239
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but having worked on this before with you I thought I would add my voice to this issue...

First, you are checking for a Null when there are no Nulls there, just zeros. As was pointed out (here and in previous posts) Null and zero are two completely different things. When you check for a Null when it does not ever exist, the response is always false and the second calculation is used. Since the denominator is zero when the Total1 is zero (and a zero takes you to the second part of the IIf response because it is not Null), you get the error condition.

So, just check for zero instead ...

=IIf([total1]=0,0,[total2]/([total1]*0.1)/100)

Second, I do not understand your calculation. What are you trying to do here?

HTH
Joe
Attached File(s)
Attached File  TechSupport_3_Rev5.zip ( 111.7K )Number of downloads: 1
 
Go to the top of the page
 
Scooter1
post Feb 5 2018, 03:22 PM
Post#7



Posts: 62
Joined: 8-January 18



RJD and UA,

Thank you all for this assistance. I was out of town on a special assignment to assist one of our branch, they have Certification/Accreditation coming up, so they needed some guidance. It was a fun assignment, I had a chance to step away from the desk and see something different.


Now I'm back. I will be checking this latest from RJD and if I have any more crazy questions, I will certainly come back for your help.


Again, thank you all.


RJD, that calculation was posted incorrectly. Thank you for all your assistance.


Go to the top of the page
 
RJD
post Feb 5 2018, 03:40 PM
Post#8


UtterAccess VIP
Posts: 9,239
Joined: 25-October 10
From: Gulf South USA


You are very welcome. We are all happy to assist. Let us know if we can assist further.

Regards,
Joe
Go to the top of the page
 
Scooter1
post Feb 7 2018, 03:32 PM
Post#9



Posts: 62
Joined: 8-January 18



RJD,

Just when I thought I am done for good, I ran into another one.

I now have a date range for the report. What is not happening is that it only pulls records with services done to it. What should have been the correct way is filter by date, and still shows everyone who are assigned to that dept, regardless if they have no record or not. The members of each dept should be in the report even if they have zero values.


Please assist, file attached.
Attached File(s)
Attached File  TechSupport_3_Rev3__2_.zip ( 72.84K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Feb 7 2018, 11:08 PM
Post#10


UtterAccess VIP
Posts: 9,239
Joined: 25-October 10
From: Gulf South USA


Hi again: Well, to limit the tickets with closed dates, you are going about this the wrong way. By using the tickets table in the query and limiting there, you are limiting the total records in the query to just those within that date range. You need a separate query that limits tickets to that date range, then include that in the report query instead of the table. See the revision to your db attached.

Beyond that I am puzzled what you are actually trying to show on that report. Shouldn't you be limiting the ticket count in the query that already feeds the report query, instead of bringing in the ticket table data, even with the new query? And what about the other value?

HTH
Joe
Attached File(s)
Attached File  TechSupport_3_Rev6.zip ( 54.72K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Feb 8 2018, 11:29 AM
Post#11


UtterAccess VIP
Posts: 9,239
Joined: 25-October 10
From: Gulf South USA


Perhaps this is more like what you are trying to do. See the report for the date range, then trace the queries back to the criteria point. Also study the joins, which needed to be corrected. You should also put the date range on the report itself...

HTH
Joe
Attached File(s)
Attached File  TechSupport_3_Rev7.zip ( 55.13K )Number of downloads: 6
 
Go to the top of the page
 
Scooter1
post Feb 10 2018, 04:39 PM
Post#12



Posts: 62
Joined: 8-January 18



RJD,

You are correct, I was doing this thing incorrectly. Thanks for all your assistance. I saw what you did with the date range query. I appreciate that very much.
Go to the top of the page
 
RJD
post Feb 10 2018, 05:25 PM
Post#13


UtterAccess VIP
Posts: 9,239
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Good luck with your project.

Regards,
Joe
Go to the top of the page
 
Scooter1
post Feb 12 2018, 06:06 PM
Post#14



Posts: 62
Joined: 8-January 18



RJD,

I turned in the project and was requested to add a new field. Well, did everything, followed all the sample queries and it went right back to square one.

When I added field "reviewed" and created its own query, and sum the value of the yes/no checkboxes, then did the report filtering and BOOM! Houston we have a problem.

The entries are duplicated and then there is the erroneous value of 2? when it should be only 1 for each service ticket? Whew, this is something else.

I filtered the report using 01/01/2018 to 01/31/2018 and BOOM here comes the duplicate entries!!!



Attached File(s)
Attached File  TechSupport_3_Rev7__2_.zip ( 100.99K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Feb 12 2018, 09:24 PM
Post#15


UtterAccess VIP
Posts: 9,239
Joined: 25-October 10
From: Gulf South USA


You put the review count in the wrong place. It goes farther back in the process. Trace the queries back to where you find where it was included, then the results brought forward.

And I assumed you would want only one review per ticket, not per service. If that is not correct you will have to adjust the Max to a Sum. The totals may not even be what you are expecting - but hard to check since the data are so scarce.

HTH
Joe
Attached File(s)
Attached File  TechSupport_3_Rev8.zip ( 48.33K )Number of downloads: 2
 
Go to the top of the page
 
Scooter1
post Feb 12 2018, 10:05 PM
Post#16



Posts: 62
Joined: 8-January 18



RJD,

Thank you!!! this is what I need. Exactly on point! Thank you!
Go to the top of the page
 
Scooter1
post Feb 12 2018, 10:27 PM
Post#17



Posts: 62
Joined: 8-January 18



RJD,

Sorry, I ran few more tests and following staffID were repeated, it does look like they were duplicates. staffID: 2, 8, 13 so every time I try 12/01/2017--12/31/2017 I get these staffID's duplicate records. Hope you could point me in the right direction. Thank you.
This post has been edited by Scooter1: Feb 12 2018, 10:27 PM
Go to the top of the page
 
RJD
post Feb 12 2018, 10:29 PM
Post#18


UtterAccess VIP
Posts: 9,239
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that was what you needed. thumbup.gif

Study the flow of the queries. Understanding this flow, where and how the counts are generated, is a key concept in developing queries in Access - and in relational databases in general. And this will come in handy for you in the future ...

Good luck with your project.

Regards,
Joe
Go to the top of the page
 
RJD
post Feb 12 2018, 10:31 PM
Post#19


UtterAccess VIP
Posts: 9,239
Joined: 25-October 10
From: Gulf South USA


Just saw your last post. I'll take a look and see what is going on...

Joe
Go to the top of the page
 
RJD
post Feb 12 2018, 10:47 PM
Post#20


UtterAccess VIP
Posts: 9,239
Joined: 25-October 10
From: Gulf South USA


QUOTE
Sorry, I ran few more tests and following staffID were repeated, it does look like they were duplicates. staffID: 2, 8, 13 so every time I try 12/01/2017--12/31/2017 I get these staffID's duplicate records. Hope you could point me in the right direction.

You'll have to give me more details. I ran the report for that date range and don't see any duplicate staff names. Where do you see duplicates?

And I only see data for Tony Mann. All others are zeros all across.

Joe
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2018 - 11:01 PM