Date between in query is causing error "expression is typed incorrectly, or it is too complex to be evaluated" (1 Viewer)

[adhoustonj]

I have 2 subforms that both have a date between query, but one of them throws the "expression is typed incorrectly, or it is too complex to be evaluated" error. If i remove the where clause, then the query runs fine. I've tried adding the parameters statement as I see that solves it for some people, but it is not working for me. Any clues on what is causing this error?

PARAMETERS [Forms]![frmReports]![bdate] DateTime, [Forms]![frmReports]![edate] DateTime;
SELECT tblProduction.prd_id, tblProduction.stat_id, tblWI.wi, tblDowntime.dt_str, tblDowntime_reasons.reason, 
tblDowntime.dt_stp, DateValue([dt_str]) AS pdate, DateDiff("n",[dt_str],[dt_stp]) AS dt
FROM tblWI INNER JOIN (tblProduction INNER JOIN (tblDowntime_reasons INNER JOIN tblDowntime ON tblDowntime_reasons.dtr_id = tblDowntime.dtr_id) 
ON tblProduction.prd_id = tblDowntime.prd_id) ON tblWI.wi_id = tblProduction.wi_id
WHERE (((DateValue([dt_str])) Between [Forms]![frmReports]![bdate] And [Forms]![frmReports]![edate]))
ORDER BY tblProduction.prd_id, tblProduction.prd_str;

 

[The_Doc_Man]

I think the answer has to depend on what is actually in [dt_str], [bdate], and [edate]. It is possible that it is complaining because [bdate] and/or [edate] could be null and probably ARE in string format. Check for nulls in the data set first. If that fails, use CDate() (or better yet, Nz(CDate()) to coerce [bdate] and [edate] to be interpreted as dates.

 

[adhoustonj]

I think the answer has to depend on what is actually in [dt_str], [bdate], and [edate]. It is possible that it is complaining because [bdate] and/or [edate] could be null and probably ARE in string format. Check for nulls in the data set first. If that fails, use CDate() (or better yet, Nz(CDate()) to coerce [bdate] and [edate] to be interpreted as dates.

Looks like I did have null values in [dt_str] that I missed.. I modified the query to include where [dt_str] is not null and now it works as intended. Thanks!

 

[The_Doc_Man]

Glad to help!