Filter data between two dates within a form

[jleval]

I have a form that has a lot of information. What I would like to do is have a command button that allows perhaps a dialog box to pop up where I would enter two dates. After the dates have been entered I would like my form to display only those records within those two dates. Any ideas?

 

[jleval]

Is this question really that tough?

 

[boblarson]

Is this question really that tough?

Nope, not tough at all.

1. Create a form with two text boxes for inputing the Start Date and End Date.

2. Have a button on that form which sets its visible property to False when the button is clicked and the rest of the code to open the other form would be:

Dim strWhere As String
 
strWhere = "[DateFieldNameHere] Between " & Format(Me.txtStartDate, "\#mm\/dd\/yyyy\#) & " And " & Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
 
DoCmd.OpenForm "YourFormYouwantToOpen", acViewNormal, , strWhere

And DateFieldNameHere is the name of the date field you are wanting to limit by on the form you are opening.

 

[jleval]

Works great, thanks

 

[Allen90]

does not work for me
it keeps giving me all the record regardless to the values, and always asks me to enter a parameter to my [datefield]

can someone tell me what am I doing wrong here?

 

[boblarson]

does not work for me
it keeps giving me all the record regardless to the values, and always asks me to enter a parameter to my [datefield]

can someone tell me what am I doing wrong here?

You are either having a spelling error or the field does not exist in the record source. Post the exact SQL you are trying.

 

[Allen90]

Well, it's kinda the same jleval wrote, only difference is that I wrote it in the Code Builder of the button, and didn't do that thingy with the visible something false cuase I'm now to this and wasn't sure if it was that neccacary.
And I'm not sure I know what SQL is or how to get there.
so.... can you help me here?

 

[boblarson]

Well, it's kinda the same jleval wrote, only difference is that I wrote it in the Code Builder of the button, and didn't do that thingy with the visible something false cuase I'm now to this and wasn't sure if it was that neccacary.
And I'm not sure I know what SQL is or how to get there.
so.... can you help me here?

Can't help if you don't post what I asked you to post.

 

[Allen90]

private sub command11_click()
Dim strWhere As String

strWhere = "[field12] Between " & Format(Me.StartDate1, "\#mm\/dd\/yyyy\#) & " And " & Format(Me.EndDate1, "\#mm\/dd\/yyyy\#")

DoCmd.OpenForm "commprob", acViewNormal, , strWhere
end sub

 

[boblarson]

So you have a field in your table that the form uses as its record source named Field12? Is that in the table or is the form using a query for its record source?

 

[Allen90]

well its a field ment to display only a record and all of that form fields are locked.
and that field (field12) is in another form which it's source is from it's table (my datebade)

 

[boblarson]

well its a field ment to display only a record and all of that form fields are locked.
and that field (field12) is in another form which it's source is from it's table (my datebade)

1. You can't filter on a field that doesn't exist in the record source of the form that is being opened.

2. A field is something found only in a table or query. A CONTROL can be bound to the field but a field is NOT ON/IN a form.

 

[Allen90]

Oh, so basically I need to rely my filtering form to the one being filtered when both are "under" the same table, right?

 

[boblarson]

Oh, so basically I need to rely my filtering form to the one being filtered when both are "under" the same table, right?

Not sure I understand that statement.

 

[kheekhay15]

Hi,

I have a similar query.

I have a form with a subform based on a query. There are many fields in that query. On the form, there are certain criterias which when typed onto the box or selected from a combo box will update the query which in turn will update the subform. By the way, there is a "SEARCH" button that when pressed runs the command Me.[subformname].Requery.

My only problem now is the date. I have a field in my table named DateRecalled. I have two text boxes in my main form for the start date and end date. What code or criteria in my query do I need to put so that when I requery, it will filter all dates from the two text boxes for the dates.

In my query named qrybuilder, I put this as a criteria on the fields: Like "*" & [Forms]![frmQueryBuilder]![cboSex] & "*"

The [cboSex] changes depending on the field.

xxx.facebook.com/photo.php?fbid=4954018855274&set=a.1260047668303.40780.1443003309&type=1&relevant_count=1&ref=nf

Any ideas?

 

[SparkieDEV]

Nope, not tough at all.

1. Create a form with two text boxes for inputing the Start Date and End Date.

2. Have a button on that form which sets its visible property to False when the button is clicked and the rest of the code to open the other form would be:

Dim strWhere As String

strWhere = "[DateFieldNameHere] Between " & Format(Me.txtStartDate, "\#mm\/dd\/yyyy\#) & " And " & Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")

DoCmd.OpenForm "YourFormYouwantToOpen", acViewNormal, , strWhere

And DateFieldNameHere is the name of the date field you are wanting to limit by on the form you are opening.

Hi, does this code also work on opening a form under BETWEEN date conditions? I tried the following below to no avail.

    Dim strWhere As String

    strWhere = "[TBLRequisitionsDB]![DateREQ] Between " & Format(Me.DateFROM, "\#dd\/mm\/yyyy\#) & " And " & Format(Me.DateTO, "\#dd\/mm\/yyyy\#")

    DoCmd.OpenReport "MEICA Requisitions Report", acViewNormal, , strWhere

 

[pbaldy]

Use this to see what the variable contains after it's set:

Debugging

You probably need to to use US format for the date.

 

[pbaldy]

On format:

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com