Using the NZ Function for a count query 1

[AaronConcannon]

Hi,

I'm developing a database that contains a lot of calculations, mostly based on counts of yes/no tick field boxes.

The problem I'm having is that if none of the records selected by the query have a yes value, my query returns a null, which stops a calculation in a different query working.

I've looked at the access help on the NZ function, but I can't make sense of it. Could someone give me an "idiots guide" to how to add the NZ function to a count query

TIA

Aaron

 

[Quehay]

You give the NZ function a value parameter and a "do this if the parameter value is null" argument:

Nz(MyVal,Arg)

So if you're expecting a numerical value in MyVal and want to convert the null to zero if such arises (Null to Zero is the origin of the function name), you write:

Nz(MyVal, 0)

You can make a null string into a zero-length string:

Nz(NyVal,"&quot

You can also test for whether or not the function was invoked(if the value was null you get a True)--here it's nested within an Iif function:

Iif(Nz(MyVal)="", "No Value",MyVal)

Hope this is useful

 

[AaronConcannon]

Hi,

Thanks for your help, but I still can't get it to work, could you have a look at this code and see if there's anything glaringly wrong with it.

Expr1: NZ("[CountOfLunchtime workshops (General Classroom assistants)]/[CountOfProfile ID]*100",0)

This is copy and pasted straight out of query design view

 

[Quehay]

In general there shouldn't be double quote marks surrounding anything that represents a field value rather than a literal. (Also, you wouldn't use Nz with a literal since you already know what the value is.) I'm having a hard time figuring out what's in your test parameter--are these fields or functions applied to fields?

 

[AaronConcannon]

hi,

[CountOfLunchtime workshops (General Classroom assistants)]
is a field from another query, that counts the number of general classroom assistants that are involved in lunchtime workshops.

[CountOfProfile ID] Is again a field from another query, this query counts the total number of records in the table.

the query I'm trying to use the NZ function in takes these two fields devides the first by the second and then multiplies by 100 to get a percentage value. I need the result displayed as a 0 instead of a null.

Thanks for your help so far.

 

[Quehay]

Aaron,

If the value brought in as [Count of Profile ID] could be zero in any case you'll still get a "division by zero" error, regardless of the use of Nz at this level. So if that could be a zero use Nz in that preceding query, i.e.,
in the field box in query design window:

|ProfileIDCnt: Nz([ProfileID],0)|
| Count |

Multiplication & division have equal value, so the left to right order will prevail in your equation, but for visual clarity I'd use parentheses to separate the division operation from the multiplication by 100.

I hope this gets you going!

 

[AaronConcannon]

Hi,

Thanks again for your continuing support, it's really appreciated.

the profile id field will never be a zero, it's a count of the records in the main table of the the database. it's the other half of the query that's causing the problem. This is how my query now looks

|Lunchtime workshops (General Classroom assistants)|
|group by|
|criteria = yes|

(next to this field is)

|Expr1: Nz([Lunchtime workshops (General Classroom assistants)],0)|
|count|

This still returns a null! I'm close to tearing my hair out, can you see why this still doesn't work?

In case it's any help in solving the problem, here is the contents of SQL view as well

SELECT [School SEN profile and financial information].[Lunchtime workshops (General Classroom assistants)], Count(Nz([Lunchtime workshops (General Classroom assistants)],0)) AS Expr1
FROM [School SEN profile and financial information]
GROUP BY [School SEN profile and financial information].[Lunchtime workshops (General Classroom assistants)]
HAVING ((([School SEN profile and financial information].[Lunchtime workshops (General Classroom assistants)])=Yes));

Thanks again

Aaron

 

[Quehay]

I still don't read the ".[LunchtimeWorkshops (General Classroom Assistants)]" and understand it (others probably would recognize the structure faster than I do). Is this one field or a derived value based on a table field?

 

[AaronConcannon]

[LunchtimeWorkshops (General Classroom Assistants)] Is a single field, it's a yes/no tick box Aaron Concannon
Aaron.concannon@BADSPAMBOTgmx.net
Remove BADSPAMBOT to reply by mail