Filter data between two dates within a form (1 Viewer)

jleval

Registered User.
Local time
Today, 05:50
Joined
May 16, 2012
Messages
53
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

Registered User.
Local time
Today, 05:50
Joined
May 16, 2012
Messages
53
Is this question really that tough?
 

boblarson

Smeghead
Local time
Today, 04:50
Joined
Jan 12, 2001
Messages
32,059
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:

Code:
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.
 

Allen90

New member
Local time
Today, 13:50
Joined
Aug 9, 2012
Messages
9
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

Smeghead
Local time
Today, 04:50
Joined
Jan 12, 2001
Messages
32,059
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

New member
Local time
Today, 13:50
Joined
Aug 9, 2012
Messages
9
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? :D
 

boblarson

Smeghead
Local time
Today, 04:50
Joined
Jan 12, 2001
Messages
32,059
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? :D

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

Allen90

New member
Local time
Today, 13:50
Joined
Aug 9, 2012
Messages
9
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

Smeghead
Local time
Today, 04:50
Joined
Jan 12, 2001
Messages
32,059
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

New member
Local time
Today, 13:50
Joined
Aug 9, 2012
Messages
9
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

Smeghead
Local time
Today, 04:50
Joined
Jan 12, 2001
Messages
32,059
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

New member
Local time
Today, 13:50
Joined
Aug 9, 2012
Messages
9
Oh, so basically I need to rely my filtering form to the one being filtered when both are "under" the same table, right?
 

kheekhay15

New member
Local time
Today, 19:50
Joined
Sep 21, 2012
Messages
5
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

New member
Local time
Today, 11:50
Joined
Oct 13, 2020
Messages
26
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:

Code:
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.





Code:
    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

Wino Moderator
Staff member
Local time
Today, 04:50
Joined
Aug 30, 2003
Messages
36,118
Use this to see what the variable contains after it's set:


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

Users who are viewing this thread

Top Bottom