Searching between dates fails

[MilaK]

Hello,

There are two date fields txtStartDate and txtEndDate on a form that filters a sub-form. This this the query behind the sub-form:

PARAMETERS [Forms]![frm_search_comments]![txt_gene] Text ( 255 ), [Forms]![frm_search_comments]![txt_variant] Text ( 255 ), [Forms]![frm_search_comments]![txt_cnvs] Text ( 255 ), [Forms]![frm_search_comments]![txt_fusions] Text ( 255 ), [Forms]![frm_search_comments]![txt_fusion_imb] Text ( 255 ), [Forms]![frm_search_comments]![cb_tumor_type] Text ( 255 ), [Forms]![frm_search_comments]![txtStartDate] DateTime, [Forms]![frm_search_comments]![txtEndDate] DateTime;
SELECT tbl_reportable_comments.variant_id, tbl_reportable_comments.tumor_type, tbl_reportable_comments.variants, tbl_reportable_comments.cnvs, tbl_reportable_comments.fusions, tbl_reportable_comments.fusions_imbalance, tbl_reportable_comments.CreatedDate, tbl_reportable_comments.LastReviewed, tbl_reportable_comments.CreatedBy, tbl_reportable_comments.Interpretation, tbl_reportable_comments.CommentTxt
FROM tbl_reportable_comments
WHERE (((tbl_reportable_comments.variant_id) Like "*" & [Forms]![frm_search_comments]![txt_gene] & "*") AND ((tbl_reportable_comments.tumor_type) Like "*" & [Forms]![frm_search_comments]![cb_tumor_type] & "*") AND ((tbl_reportable_comments.variants) Like "*" & [Forms]![frm_search_comments]![txt_variant] & "*") AND ((tbl_reportable_comments.cnvs) Like "*" & [Forms]![frm_search_comments]![txt_cnvs] & "*") AND ((tbl_reportable_comments.fusions) Like "*" & [Forms]![frm_search_comments]![txt_fusions] & "*") AND ((tbl_reportable_comments.fusions_imbalance) Like "*" & [Forms]![frm_search_comments]![txt_fusion_imb] & "*") AND ((tbl_reportable_comments.CreatedDate)>=[Forms]![frm_search_comments]![txtStartDate] And (tbl_reportable_comments.CreatedDate)<=[Forms]![frm_search_comments]![txtEndDate]));

The query is executed on the loading event of the main form:

On Error GoTo LoadForm_Err

Dim SourceQuery As String
Dim CrsRs As DAO.Recordset
Dim qdef As DAO.QueryDef

SourceQuery = "qry_Report_Comments"
Set qdef = CurrentDb.QueryDefs(SourceQuery)
qdef.Parameters("[Forms]![frm_search_comments]![txtStartDate]") = [Forms]![frm_search_comments]![txtStartDate]
qdef.Parameters("[Forms]![frm_search_comments]![txtEndDate]") = [Forms]![frm_search_comments]![txtEndDate]

qdef.Parameters("[Forms]![frm_search_comments]![txt_gene]") = [Forms]![frm_search_comments]![txt_gene]
qdef.Parameters("[Forms]![frm_search_comments]![cb_tumor_type]") = [Forms]![frm_search_comments]![cb_tumor_type]
qdef.Parameters("[Forms]![frm_search_comments]![txt_variant]") = [Forms]![frm_search_comments]![txt_variant]
qdef.Parameters("[Forms]![frm_search_comments]![txt_cnvs]") = [Forms]![frm_search_comments]![txt_cnvs]
qdef.Parameters("[Forms]![frm_search_comments]![txt_fusions]") = [Forms]![frm_search_comments]![txt_fusions]
qdef.Parameters("[Forms]![frm_search_comments]![txt_fusion_imb]") = [Forms]![frm_search_comments]![txt_fusion_imb]


Me.frm_report_fields.Form.RecordSource = SourceQuery

'Me.RecordSource = SourceQuery
Set CrsRs = qdef.OpenRecordset


CrsRs.Close

The form shows no data even though all the search fields are blank.

Do you know how to fix this issue?

Thanks, Mila

 

[Galaxiom]

Try this:

Set Me.Recordset = qdef.OpenRecordset

 

[MilaK]

The same result, no data is shown when the form loads. When I try to enter values into the fields and reload the form I get:
"You've entered an expression that has invalid reference to the property of Form/Report"

Does this have anything to do with the field type? The form has a short date.

The query works fine with all the other fields but not the date fields.

Thanks