Null help

[gfoster9055]

I new to access and need some help, here is my problem. I have a query that gives data to a table, the only problem is that there is blank fields in the query and from what I read that is not a good thing when doing calculations. Here is the formula that I am using in the query
DaystoCloseNonQSF: IIf([QSFNonQSF]<>"qsf",CalcWorkdays([DateReceived],[DateCompleted]),"")
Then I need to get the average of the days that this formula returns, since there is blank fields it won’t. Someone suggested that I use the Nz function, and I can't get it to work.
I hope that this makes sense.

Thanks
Greg

 

[PHV]

Have you the source of the CalcWorkdays function ?
It should handle exceptions more nicely ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gfoster9055]

This code is what I am using in the control source in the form DaystoCloseNonQSF: IIf([QSFNonQSF]<>"qsf",CalcWorkdays([DateReceived],[DateCompleted]),"")
I am also using the same in the query.

Thanks
Greg

 

[PHV]

I rephrase: what is CalcWorkdays ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gfoster9055]

CalcWorkdays is a call to a module that only counts monday through Friday. The forumla counts how many days between start and finish minus weekends. I have a query do the same thing but now I need to find the average of all "qsf" days. I was trying to have a query write to the tables, but I believe since there is blank fields that it only returns "0" to the table. Like I said I am new to this so please bear with me. I could send you a copy of the db if that would help.

Thanks
Greg

 

[dhookom]

Your IIf() function might return a number or might return a string. You need to make up your mind. I assume you want to Sum or Avg the value returned by the IIf(). If so, replace the [red]""[/red] with either 0 or Null.

DaystoCloseNonQSF: IIf([QSFNonQSF]<>"qsf",CalcWorkdays([DateReceived],[DateCompleted]),0)
or
DaystoCloseNonQSF: IIf([QSFNonQSF]<>"qsf",CalcWorkdays([DateReceived],[DateCompleted]),Null)

An expression or function should always return a specific data type if possible. The only exception that I allow is the possibility of returning a Null.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]