Solved How to create a search filter using a "from" and "to" date range picker on a continuous form (1 Viewer)

[SparkieDEV]

Hi all,

I have a continuous form with a whole bunch of dates. I would like to create a search filter with the criteria "from" date and "to" date using a date picker. I have not yet designed this, so do not have any existing code.

The reason behind this request is so that I can create a report based on two date ranges. Typically, on a monthly basis but not strictly to that assumption. Can anyone suggest options please.

 

[theDBguy]

Hi. You can use a Form reference to get a value from a form control like a Textbox. The syntax is something like.

Forms!FormName.ControlName

Sent from phone...

 

[CJ_London]

or if applying a filter via clicking a button

me.filter="[someDate] between #" & format(fromDate,"mm/dd/yyyy") & "# AND #" & format(toDate,"mm/dd/yyyy") & "#"
me.filteron=true

 

[SparkieDEV]

Update:
I have decided that I will use a click event when creating a report from a continuous form. I have a button for my report, and I have two text boxes as date pickers. where the filter condition should get the filter parameters from. This should help clarify what conditions I have to enable a filter to work.

 

[theDBguy]

Update:
I have decided that I will use a click event when creating a report from a continuous form. I have a button for my report, and I have two text boxes as date pickers. where the filter condition should get the filter parameters from. This should help clarify what conditions I have to enable a filter to work.

If you're using a button to open the report, check out the WhereCondition argument of the OpenReport method.

Sent from phone...

 

[SparkieDEV]

I have a runtime error "3000" reserved error "3201"

Private Sub BTN_REQReport_Click()
    Me.Filter = "[DateREQ] between #" & Format(DateFROM, "dd/mm/yyyy") & "# AND #" & Format(DateTO, "dd/mm/yyyy") & "#"
    Me.FilterOn = True
End Sub

 

[theDBguy]

I have a runtime error "3000" reserved error "3201"

Private Sub BTN_REQReport_Click()
    Me.Filter = "[DateREQ] between #" & Format(DateFROM, "dd/mm/yyyy") & "# AND #" & Format(DateTO, "dd/mm/yyyy") & "#"
    Me.FilterOn = True
End Sub

Hi. Try the Format() using "yyyy-mm-dd"

 

[Pat Hartman]

If both fields are date data types, do not format them. Formatting a date turns it into a string and Access will treat it like a string so:
01/10/2020 will be less than 02/10/2019. Strings are compared character by character left to right.

If you EVER need to format a date to be used in an expression, the better choice is yyyy/mm/dd as theDBGuy suggested but not formatting at all is the best choice.

 

[CJ_London]

@SparkieDEV I put the format as mm/dd/yyyy, you have changed it to dd/mm/yyyy

when building a sql string, you need to use the US date format, or the version suggested by DBG - and surround with the # character so the query engine knows that what is between them it to be treated as a date. With regards your error - I presume a) what you input are valid dates and b) the controls are not bound (i.e. the controlsource properties are blank - they should be).

However you are now saying the button opens a report, not filter the current form. So I suspect the button is on the report, not the form. If it is on the form then your code would be something like

docmd.openreport "ReportName",,,"[someDate] between #" & format(fromDate,"mm/dd/yyyy") & "# AND #" & format(toDate,"mm/dd/yyyy") & "#"

@Pat Hartman - I agree with you if comparing date datatypes, table to table, but this is building a string to find records where a date in a table is between two dates specified on a form . In the UK a date obtained from a form control is a string dd/mm/yyyy so we have to convert to US style

 

[Pat Hartman]

Yes, CJ, this is a string. Sorry i wasn't clear. It feels like we've had this situation 3-4 times just this week.