Where Clause Error In The DoCmd.OpenReport

[Galaxiom]

If Nulls are the problem change the Record Source query of the report to include:
Is Not Null on the date field.

Now there is a thought. We have been talking about the table. Perhaps the Record Source query of the report is really the problem. This would explain why your other query works but not the report. I think you may have unwittingly changed a field format in that query.

Otherwise I think you need to find the problem entries in the table and fix them. (If that is really the problem.)

First thing I would try is to open the table and order by the date field.
Hopefully the odd ones would either go to the start or the end.

Then try running a query with IsError(datefieldname) as the where clause.

Cut down the table to see if the clause will run on even one record. If it does then try adding sections of the records until it breaks.

 

[cheer]

If Nulls are the problem change the Record Source query of the report to include:
Is Not Null on the date field.

Now there is a thought. We have been talking about the table. Perhaps the Record Source query of the report is really the problem. This would explain why your other query works but not the report. I think you may have unwittingly changed a field format in that query.

Otherwise I think you need to find the problem entries in the table and fix them. (If that is really the problem.)

First thing I would try is to open the table and order by the date field.
Hopefully the odd ones would either go to the start or the end.

Then try running a query with IsError(datefieldname) as the where clause.

Cut down the table to see if the clause will run on even one record. If it does then try adding sections of the records until it breaks.

I've detected the root cause. Thanks for your guide above.

 

[Galaxiom]

I've detected the root cause. Thanks for your guide above.

And it was...........?

 

[cheer]

In the query, there are 2 important date field which is known as "StartDate" and "EndDate".

There is another field showing the duration in hour between the "StartDate" and "EndDate", which is known as "Duration" [example: Duration: datediff("m",StartDate,EndDate) ]

To execute the query regardless the date field format, query can show the output instantly with all the above fields .

To execute the report with the elimination of the Duration field through the VBA code mentioned earlier, report can be shown without any error message. However, after include the "Duration" field in the report, the error message appear as explained in earlier post.

Finding:
1) There are some EndDate value contain the NULL value.

2) Due to the "Duration" value is the gap between the StartDate and EndDate through the datediff function , thus there isn't any error shown in the query, however, the error message alert in the report (although report is generated through the query)

Solution:
Replace the existing Duration field from
datediff("m",StartDate,EndDate) to iif(isnull(StartDate) or isnull(EndDate),0,datediff("m",StartDate,EndDate))