I have a form with two fields, one field is DateFrom and the other field is DateTo. The input mask for both fields is 00/00/0000 and the format for both is Short Date.
I have a button that I click on the form and the button searches in a databse table for records with the dates between FromDate and ToDate. The underlying code that I havein the button is as follows:
Dim strWhere As String
If IsNull([FromDate]) Then
strWhere = "[Date_Rec] Is Not Null"
Else
strWhere = "[Date_Rec] >= " & Format$(Forms![Status]![FromDate], "ShortDate") & _
" AND [Date_Rec] <= " & Format$(Forms![Status].[ToDate], "ShortDate")
End If
Me.Visible = False
DoCmd.OpenReport "STATUS REPORT", acViewPreview, , strWhere
This code opens a report called STATUS REPORT. The report is generated from the same table that the button searches on. The date in the database table that we are seraching on is called Date_Rec as shown in the code above.
My problem is that when I put dates into the date fields on the form and click the button I get an error message saying:
Run-time error '5':
Invalid Procedure call or argument.
when I click debug it brings back the above code highlighted.
I have been told that it is a problem with the format "Short Date" that I am using in the code and that I need to have a date format like #dd/mm/yyyy# in order for the filter to take place when I click the button. I don't know how to do this, even if this is the solution.
Any help or suggestions very appreciated.
I have a button that I click on the form and the button searches in a databse table for records with the dates between FromDate and ToDate. The underlying code that I havein the button is as follows:
Dim strWhere As String
If IsNull([FromDate]) Then
strWhere = "[Date_Rec] Is Not Null"
Else
strWhere = "[Date_Rec] >= " & Format$(Forms![Status]![FromDate], "ShortDate") & _
" AND [Date_Rec] <= " & Format$(Forms![Status].[ToDate], "ShortDate")
End If
Me.Visible = False
DoCmd.OpenReport "STATUS REPORT", acViewPreview, , strWhere
This code opens a report called STATUS REPORT. The report is generated from the same table that the button searches on. The date in the database table that we are seraching on is called Date_Rec as shown in the code above.
My problem is that when I put dates into the date fields on the form and click the button I get an error message saying:
Run-time error '5':
Invalid Procedure call or argument.
when I click debug it brings back the above code highlighted.
I have been told that it is a problem with the format "Short Date" that I am using in the code and that I need to have a date format like #dd/mm/yyyy# in order for the filter to take place when I click the button. I don't know how to do this, even if this is the solution.
Any help or suggestions very appreciated.
