Remove Error Line from Report

[AdiDoyle]

Hi all,



I have a report which is meant to show upcoming appointments with some information about the appointment (day, date, time, and which eye the appointment is for). All information in this report is drawn from one query.

The table in which dates are stored (Appointment) allows storage of two different types of date (ApptDate and RevDate), each of which need to be displayed separately. I've circled these in red in the picture of the query design below. The same table stores information about which eye the appointment is for. I've circled this in blue. Every entry in the Appointment table will have a value of either Left or Right for eye, however, each individual entry can have a value only for ApptDate or RevDate not both.

This is causing a problem in my report. In the section of the report pictured below I only want to show appointment information where there is an ApptDate, and not where there is a RevDate. The report is putting in a line at the start because there is a RevDate value in a row in the Appointment table containing Eye information. When it tries to calculate the Weekday it returns an error because there is a null value in the ApptDate.

Is there a way to prevent the report from including rows where there is no ApptDate value?

Any help would be appreciated.

Thanks, Adrian.

Report:


Query:

[aytee111]

Reports are not to be used for getting the data right, this must be done in the query. Do all the calculations there before coming in to the report.

[AdiDoyle]

The query is where I've been trying to fix it.

But I don't know of any way in a query to make a field return as null based on the value of another field. That would solve the problem.

Ie. If ApptDate = Null, Day = Null, Time = Null, Eye = Null.

[aytee111]

DayOfAppt: IIf(IsNull(ApptDate),Null,day calculation)
If ApptDate is null then set DayOfAppt to null, otherwise do the day calculation (not sure how you are doing this part but it will be the same).

These are reserved words, Day, Time, etc. You need to change them in the query.

[pbaldy]

Try this in the criteria of ApptDate:

Is Not Null

[AdiDoyle]

Try this in the criteria of ApptDate:

Is Not Null

Wouldn't that enforce a value in ApptDate? I need it to be null when there is a value for RevDate, and vice versa.

[pbaldy]

Sorry, I must have misunderstood "I only want to show appointment information where there is an ApptDate".

[AdiDoyle]

DayOfAppt: IIf(IsNull(ApptDate),Null,day calculation)
If ApptDate is null then set DayOfAppt to null, otherwise do the day calculation (not sure how you are doing this part but it will be the same).

Hi, and thanks for the help. Quick question, would that set the data in the tables to null when the query is run, or would it just show the results of the calculation as null without affecting the data in the table?

The SQL I'm using for the calculation looks like this:

Select ... WeekdayName(Weekday(Appointment.Appointment_Date-1)) AS DayName, Appointment.Appointment_Date AS ApptDate, ... From ... ;

So updating it to include your suggestion, should it look like this?

Select ... If (IsNull (Appointment.Appointment_Date), Null, WeekdayName(Weekday(Appointment.Appointment_Date-1))) AS DayName, Appointment.Appointment_Date AS ApptDate, ... From ... ;

These are reserved words, Day, Time, etc. You need to change them in the query.

They all have different names in the query. That is just what they're represented as on the report.

[AdiDoyle]

Sorry, I must have misunderstood "I only want to show appointment information where there is an ApptDate".

No worries. I appreciate the input.

[John_G]

I need it to be null when there is a value for RevDate, and vice versa.

A data constraint such as that should be enforced at the form level, when data is being entered or updated. If you have to do it at the query level, it means you have invalid data (for that constraint)

[AdiDoyle]

A data constraint such as that should be enforced at the form level, when data is being entered or updated. If you have to do it at the query level, it means you have invalid data (for that constraint)

I have enforced this at form level. I have written a macro that erases data from one if data is entered into the other.

This means that if data is entered for RevDate on the form, ApptDate will be null in the table, which is the way I want it.

The issue this creates, however, is that when I try to use ApptDate to calculate the WeekdayName it returns an error in rows where there is no information in the ApptDate field. This shows up as an appointment record on the report with data for the Eye field, an error for the Day field, and two null fields for date and time.

[aytee111]

Note the two i's - IIf, not If. The syntax looks correct.

[AdiDoyle]

Note the two i's - IIf, not If. The syntax looks correct.

Mea Culpa, I thought that was a typo. I'll give that a try so, thanks.

[AdiDoyle]

Hey aytee111,

That worked perfectly. Thanks so much for the insight.

Best,
Adrian.

[John_G]

In you original query, try something like this for ApptDate:

Iif(not IsNull(Appointment_Date), Appointment_Date, Appointment_RevDate)

In English: If the Appointment Date is not null, then use that date, otherwise use the Rev Date.