ACCESS FEED: Re: [MS_AccessPros] adding up columns in query

Senin, 21 September 2015

Re: [MS_AccessPros] adding up columns in query

Hi Bob,

When you multiply a number by 0, than answer is always zero. This is an analogy.

Arithmetic operators cannot work with nothing, each factor in an equation has to be 'something'.

Syntax

Nz(variant[, valueifnull])

NZ is simply a function that returns a value if there is no value. All that is required is the first argument. If there is no value and Access decides this is a number it will return 0 (zero). If there is no value and Access decides this is a text it will return an empty string, ""

I prefer to specify the optional second argument so it does not default to a zero-length-string if I really want a number

the thing to keep in mind when using Nz is what it will return if you don't the specify the second, optional, argument.

If you are using Nz on a field, it will be the data type of that field -- 0 for numbers (including dates), and an empty string for text or memo

Unbound textbox / combobox / listbox controls on a form are assumed to have TEXT in them... so an empty string will be returned if nothing is specified. And, if you specify something, it doesn't have to be 0 or ""

examples:
NZ( [fieldname], 0 )
NZ( [fieldname], "" )
NZ( [fieldname], #12/31/2050#)
NZ( [fieldname], "anything else")

anytime you suspect that a referenced expression might not have a value, it is a good idea to use NZ. It is also a good idea to wrap return values from dLookup in NZ in case no match was found as you can't assign nothing to something.

for instance:

dim curCommission as Currency
curCommission = Nz( DLookup( "[Commission]", "Employees", "[EmpID] = " & nz( me.[EmpID], 0) ) ,0 )

notice how NZ is used twice -- once to make sure the criteria will be evaluated, and another time around everything in case dLookup didn't return a value.

~~~
> "I don't know if anything goes here and if something does go here I don't know what it is"

that is one reason I like Nulls! They let us know that information is not known. A Zero-Length String "", on the other hand IS a value.

warm regards,
crystal

Microsoft MVP Virtual Conference 2015 -- Access Web Apps, linking to SQL Azure from Access desktop, Excel workbook, and SSMS (53 minutes)
https://channel9.msdn.com/events/MVP-Virtual-Conference/MVP-Virtual-Conference-Americas-2015/CONS2-Using-Access-and-SharePoint-for-Travel-Expenses-and-Pulling-up-Maps

~ have an awesome day ~

On 9/21/2015 9:48 AM, 'Robert Peterson' bob@alternatefinishing.com [MS_Access_Professionals] wrote:

I find a good way to wrap your head around Null is to think of it as "I don't know if anything goes here and if something does go here I don't know what it is".

If you add "I don't know" to anything it is still "I don't know" which is why we use Nz to turn the Null to zero before doing math.

Bob Peterson

.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, September 21, 2015 10:46 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] adding up columns in query

actually, you can't HAVE nulls either ... there can be Nulls!

On 9/21/2015 8:41 AM, crystal 8 wrote:

Hi Patty,

> "null values "
fyi, there is no such thing ... null is the absence of a value
... just want to make sure you learn the term properly

you can have Nulls, but you can't have Null Values

warm regards,
crystal

On 9/21/2015 7:14 AM, pattykf@cox.net [MS_Access_Professionals] wrote:

THANK you Duane

I used the Nz and I was able to get the column to sum up the data, thank you so much

I did have null values in the columns

really appreciate the additional skill

Have a wonderful day

Patty

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Patty,
Are there any Nulls in the fields? If so you must wrap the column in the Nz() function like: Nz(PsychID,0)+

Are all of the columns right-aligned when viewed in datasheet?

Also, it's near impossible for us to actually determine what's going on without seeing some of the records that display the behaviour you are suggesting.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 18 Sep 2015 14:13:35 -0700
Subject: [MS_AccessPros] adding up columns in query

I am sure I have something missing here but I have tried two ways to have the query add columns together. I checked the queries that lead up to this to make sure the fields output to general numbers but it does not add up the information, I want the psychID, the counUrgent and the CountNoSHow to add up to give me a sum of the information
any help/hints will be appreciated
thank you so much
Patty

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, ER_HOSP_Psych_related.PsychID, ER_VISITS_6MOS_3_or_More_COUNT.CountUrgent, Appts__6MOS_2_or_More_No_Show_COUNT.CountNoSHow, [PsychID]+[CountUrgent]+[CountNoSHow] AS Risk_total_
FROM ((dbo_person LEFT JOIN ER_VISITS_6MOS_3_or_More_COUNT ON dbo_person.person_id = ER_VISITS_6MOS_3_or_More_COUNT.person_id) LEFT JOIN Appts__6MOS_2_or_More_No_Show_COUNT ON dbo_person.person_id = Appts__6MOS_2_or_More_No_Show_COUNT.person_id) LEFT JOIN ER_HOSP_Psych_related ON dbo_person.person_id = ER_HOSP_Psych_related.person_id;