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
> Query With Nz Function Did Not A Correct Value, Access 2016    
 
   
Lin100
post Dec 28 2017, 09:25 AM
Post#1



Posts: 640
Joined: 20-August 06



I have a simple query that uses the NZ function as shown below
The first three have correct calculation.
The fourth which is Grand_Total have the wrong value. The number is very high even though the first three have a very low value.

1) Control Number Total: Nz([Control No To]-[Control No From]+1,0) <---- value is correct
2) Control2 Number Total: Nz([Control2 No To]-[Control2 No From]+1,0) <---- value is correct
3) Control3 Number Total: Nz([Control3 No To]-[Control3 No From]+1,0) <---- value is correct

4) Grand_Total: [Control Number Total]+[Control2 Number Total]+[Control3 Number Total] <---- value is incorrect

/////////////////////////////////////////////////////////

SELECT tblCheckVerification.[Print Date], tblCheckVerification.CheckType, Nz([Control No To]-[Control No From]+1,0) AS [Control Number Total], Nz([Control2 No To]-[Control2 No From]+1,0) AS [Control2 Number Total], Nz([Control3 No To]-[Control3 No From]+1,0) AS [Control3 Number Total], [Control Number Total]+[Control2 Number Total]+[Control3 Number Total] AS Grand_Total
FROM tblCheckVerification
WHERE (((tblCheckVerification.[Print Date]) Between [Forms]![Weekly_Monthly_Yearly_Report]![From_Date] And [Forms]![Weekly_Monthly_Yearly_Report]![To_Date]))
ORDER BY tblCheckVerification.[Print Date], tblCheckVerification.CheckType;

///////////////////////////////////////////////////////



Attached File(s)
Attached File  Check_Services_1.jpg ( 219.56K )Number of downloads: 6
Attached File  Check_Services_2.jpg ( 105.74K )Number of downloads: 8
 
Go to the top of the page
 
RJD
post Dec 28 2017, 10:40 AM
Post#2


UtterAccess VIP
Posts: 8,487
Joined: 25-October 10
From: Gulf South USA


Hi: If you look at the images you posted, you will see that the values are left justified - like text rather than numeric values. The [Grand_Total] is simply the concatenation of the three text values. As a quick fix, try converting each of these values to numeric when you add them ...

Grand_Total: CInt([Control Number Total])+CInt([Control2 Number Total])+CInt([Control3 Number Total])

But I would look further into this, and discover why the values are being created as text in the first place.

HTH
Joe


--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Lin100
post Dec 28 2017, 03:06 PM
Post#3



Posts: 640
Joined: 20-August 06



Thank you RJD for your help.
The NZ function somehow change the number to character --> Nz([Control No To]-[Control No From]+1,0)
I have to use Cint as you have suggested to change it back to number. Now it added correctly.
Go to the top of the page
 
RJD
post Dec 28 2017, 05:57 PM
Post#4


UtterAccess VIP
Posts: 8,487
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that worked for you.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 08:24 PM