Include null values in formula

[jjb373]

I have a formula in my report footer which averages the total time between two dates in my query. The only problem with this formula is, is that it does not include the Null values.
My report(query) makes any records Null which are less than 24 hours between the two dates. That way the user can view only the records which are a problem. (> 24 hours is a problem). The Null values are not displayed in the report.
External Documents = time the documents where recieved.

=Avg(DateDiff("s",[Date of Appointment],[External Documents]))

How can I include the Null values for this formula??

 

[dhookom]

Are you saying that you want to include records in your Avg() calculation that are not in your report's record source?

If so, create a totals query that calculates your value and use it as the record source for a subreport.

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]

 

[jjb373]

Not quite.

My report is created from a query. The query eliminates records where the datediff is under 24 hours for the record. It also has the user enter two different dates a start and end date so you only see records in that time frame.

We only want to view records that are over the 24 hours in the report. This eliminates many of the records in the table.

I want to create a textbox in the report footer that averages the time difference for all the records in that time frame the user entered. The total formula should avg both the records shown in the report and the records not shown in the report (the ones eliminated by the query) that are between the two desired dates.

Here is my current formula which averages all time differences in my table, I want the averages for the records between the two selected dates.

=Avg(DateDiff("s",[Date of Appointment],[External Documents]))

I hope this is all clear enough.

 

[dhookom]

I think I would include the <24 hour records in your report. Then add code to the On Format event of your detail section that would cancel the printing for records <24.

Cancel = ((Me.txtExternalDocuments-Me.txtDateofAppointment)<1)

You can then use this expression in your report footer:
=Avg(DateDiff("s",[Date of Appointment],[External Documents]))

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]

 

[jjb373]

When I try this code, I am getting an error:

Run time Error '13':
Type mismatch

Any suggestions??

 

[jjb373]

Sorry about that, disregard that last comment. Here is my problem:

Run time Error '94':
Invalid use of Null

here is my code:

Any suggestions as to why I may be getting this?
Cancel = ((Me.HX_Documents - Me.Date_of_Appointment) < 1)
I tried this too.
Cancel = ((Me.Date_of_Appointment - Me.HX_Documents) < 1)

 

[jjb373]

I believe the problem is that I have 2 different types of documents in my report.

One being HX Documents and one being External Documents.

I am displaying both.

so my results look like this

HX External
1d 3h 5min 0d 5h 36min
2d 12h 16min
0d 15h 1min 1d 0h 0min

I figure my code I can do like this:
Cancel = ((Me.HX_Documents - Me.Date_of_Appointment) < 1) And ((Me.External_Documents - Me.Date_of_Appointment) < 1)

Any suggestions if this will work and I think I am getting the 'Invalid use of null error' because one of the records does not have HX document results.

Any suggestions. Thanks for the help and sorry about the confusion.

 

[dhookom]

What do you want to happen if one of the date fields is null?

Do you know how to handle nulls in code and expressions? You would normally use Nz() to replace the Null with another value like:

Cancel = ((Nz(Me.HX_Documents,Me.Date_of_Appointment) - _
      Me.Date_of_Appointment) < 1) And _
    ((Me.External_Documents - Me.Date_of_Appointment) < 1)

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]

 

[jjb373]

If one of those document fields are null, either external or hx, I just want that field omitted.

The Date_of_appointment will never be blank or a null value. I only want it to apply to the hx_documents and external_documents.

I apprecite your time Duane.

 

[dhookom]

Basically, if either HX or External are null, you want to treat them as if they were the same as Date_of_Appointment so the formatting of the detail section is canceled. That is what Nz(...,...) does. I applied this to the HX above. You may need to apply it to the External also.

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]

 

[jc31]

If I want to omit negative values and replace them with a 0, how would I write it?

if field ="-" the "0" ??

 

[dhookom]

Replace them where? In code or in a control source? If in a control source, use something like:
=IIf([SomeExpression]<0,0,[SomeExpression])

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]

 

[jjb373]

I am getting an error when I put try using this formula in an unbound textbox in my detail section.
=IIF([HX_Documents]>1,1,[HXCT])

What I am wanting to do is, check to see if my HX Documents value is greater than 1. If it is make my unbound textbox = 1 else 0. Any way to do this I would greatly appreciate.

I wouldn't have all of these questions if I was using Crystal Reports. So much easier to get values you are looking for. But I appreciate your help.

 

[jjb373]

In the reply above I ment to say:
What I am wanting to do is, check to see if my HX_Late value is greater than 24 hours (or 1?). If it is, make my unbound textbox = 1 else 0.

Just as a note, my HX_Late value is a value that comes from:

datediff of HX Documents- Date of Appointment

 

[dhookom]

This is all fairly straight-forward. You should be able to use an expression like:

=IIf([HX_Late]>1, 1,0)

This assumes that HX_Late is a non-null, numeric value. Remember that date/time values are basically numbers.
This also assumes the name of the control in the report is not also the name of a field. This is a basic rule for bound controls.

If your column HX_Late is based on the DateDiff() expression, keep in mind the dates can't be null and the function can return different intervals of time such as month, week, day, hour, and second all as integers.

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]

 

[jjb373]

It is a very straight forward-formula. I overlooked the principle that the expression can not be null. Some of my values are null because they don't have any data entered in the HX Documents field. Date of appointment will always be available. Also, in my report, if I cancel values from showing (supress in reality) does that make them null values?


In this case do I have any type of work around or other option??

Thanks!

 

[dhookom]

I think I already explained this. You are basically wanting to identify when HX_Documents is more than 1 day (24 hours) after Date_Of_Appointment. If there is no HX_Documents value then you can treat it as the same value as Date_Of_Appointment. If this is correct, then note the expression I provided on [10 Jan 06 15:58].

HX_Late: Nz(HX_Documents,Date_of_Appointment)-Date_of_Appointment

If HX_Documents is null then HX_Late will be 0. If it is not null then HX_Late will be the number of days between the two fields.

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]