Filter data between two dates within a form

jleval

jleval

Registered User.
Local time
Today, 08:07
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?
 
Is this question really that tough?
 
jleval said:
Is this question really that tough?
Click to expand...

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.
 
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?
 
Allen90 said:
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?
Click to expand...

You are either having a spelling error or the field does not exist in the record source. Post the exact SQL you are trying.
 
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
 
Allen90 said:
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
Click to expand...

Can't help if you don't post what I asked you to post.
 
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
 
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?
 
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)
 
Allen90 said:
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)
Click to expand...

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.
 
Oh, so basically I need to rely my filtering form to the one being filtered when both are "under" the same table, right?
 
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?
 
boblarson said:
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.
Click to expand...
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
 
Use this to see what the variable contains after it's set:


You probably need to to use US format for the date.
 
You must log in or register to reply here.

Similar threads

J
Between/And
2
Replies
21
Views
652
Pat Hartman
P
G
Open Filter Dialog box
Replies
12
Views
589
LarryE
LarryE
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
237
Mike Krailo
Mike Krailo
D
Date and Time Format
Replies
12
Views
387
Pat Hartman
P
J
Solved Synchronzing Two Sub Forms to each other within a parent form
Replies
14
Views
517
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom