Another 'Zero' Query Thread! :/

[Davrini]

Hey! Having a spot of bother with my calculations, wondering if anyone can offer a quick solution!

The clients in the database occasionally have a different type of session, called a "Change Interview". I've created a query which counts the total number of Change Interviews a client has had and inputs that data into the Mega-Query (which is where ALL the data about the client it collated). The same must be done with the attendance rates (that is, to calculate the total sessions they have attends, were UTA (Unable to Attend), DNA (Did Not Attend) or Cancelled another way. All of these variables are then summed to calculate their Session Number and Attendance Rate (Data which is needed for the funding).

My problem is that in instances where the clients have not yet had a Change interview, or have not missed a session, then the queries that are run do not return a value. How can I run a query so that instead of:

Client   No.CIs
CL0001   4
CL0002
CL0003   1
CL0004
CL0005
CL0006   2
CL0007   3

It returns the 'Null' values as a Zero so the query returns the values:

Client   No.CIs
CL0001   4
CL0002   0
CL0003   1
CL0004   0
CL0005   0
CL0006   2
CL0007   3

(The same would be applied for attendance. If they haven't skipped a session, then that query returns a blank field). The problem with this is that the Formula's then do not number crunch corrently as if one of the fields is missing a number, the formula does not work.

Total Sessions: DateDiff("w",[First Session],[Last Session])-([Total UTAs]+[Total DNAs]+[Other Cancelations]+[Total CIs])

I'm trying to do this on one query so that I the data is all automatic, and does not require a form to be open for it to be adjusted. Is this possible?

 

[Trevor G]

In the query add an expression that uses an IIF statement to show 0 if no value shown.

Addname:IIF([FieldName]="",0,[FIELDNAME})

 

[Davrini]

Thank you very much!

Ironically, I had JUST figured this out by myself, haha. Realised that I did a formula to add to names together and stole the formula for that after adapting it.

Here's what I ended up with:

IIf(IsNull([CountofAppointmentDate]),0,[CountofAppointmentDate])

Which is exactly what you said! Many thanks either way for the quick reply! Hopefully this will serve someone else who, like myself, is an Access newbie!

 

[Trevor G]

Pleased to have helped Davrini,

Thanks for letting me know you have a working solution.