Date filter

[Chris75 via AccessMonster.com]

Hi AccessVandal,

Neither worked.

Although I am seemingly closer to where I was.

Clean slate:

Major form is called Employees.

Subform is called schedule.

No issues with the relationship.

In the subform, I have the fields WorkDate, In and Out from the table.
I also have the unbound fields :TotalHrs, BeginDate and EndDate.

I want to filter dates in the subform such that when a date range is entered,
the records are updated automatically leaving only records that correspond to
the date range entered.

It was suggested that the subform be based on a query. However, the criteria
aspect didn't work for me because when I entered in the subform, I was
immediately asked to enter the BeginDate and EndDate. That is not what I
wanted. I want to view the subform. Be able to enter or modify dates, times,
etc. I also want to be able to enter the date range and then have the record
updates. This could be from a command button or pressing enter, although the
button maybe preferable.

Any suggestions?

I have to say thank you to everyone who has given there input.

Since your form is bound to the table, why not just use the Form's filter?

Create a button near the textboxes..something like

Private Sub YourCommandButtonName_Click()
Me.Filter = "SomeDate Between " & Me.StartDate & " AND " & Me.EndDate
Me.FitlerOn = True
End Sub

Not sure of the ocotorp/hash thingy, if don't work try

"EndDate Between #" & Me.StartDate & "# AND #" & Me.EndDate & "#"

Hello,

[quoted text clipped - 12 lines]

Chris

 

[AccessVandal via AccessMonster.com]

You didn't say you had a subform and didn't say you want to filter the
subform.

Where is the command button, in the main form or the subform?

In order for the Form filter to work, you must bind the subform’s
recordsource to the table or a query without any filter criteria.

The unbound fields: TotalHrs, BeginDate and EndDate, where are these controls?

They should not be located in the subform. Put them in the mainform's Header
or any where in your mainform.

The code I gave previously will not work on the subform but will work on the
main form. If the command button is in the main form, change it to

'-----------------code------------------------------
Private Sub YourCommandButtonName_Click()
Me.schedule.Form.Filter = "SomeDate Between #" & Me.StartDate & "# AND #" &
Me.EndDate & "#"
Me.schedule.Form.FitlerOn = True
End Sub
'--------------end of code-----------------------

Watch for word wrap in your browser.

 

[Chris75 via AccessMonster.com]

Hi Vandal,

I got the code to work partially. When I clicked the command button, the
filter response was correct except that I wanted a 2nd popup to appear to
enter the 2nd date. As it stands now, I have the 1st "SomeDate" popup.
Basically that would be my 1st "StartDate" popup. The 2nd popup would give
me my "EndDate". Should I repeat the code to enable this?

Thanks!

You didn't say you had a subform and didn't say you want to filter the
subform.

Where is the command button, in the main form or the subform?

In order for the Form filter to work, you must bind the subform’s
recordsource to the table or a query without any filter criteria.

The unbound fields: TotalHrs, BeginDate and EndDate, where are these controls?

They should not be located in the subform. Put them in the mainform's Header
or any where in your mainform.

The code I gave previously will not work on the subform but will work on the
main form. If the command button is in the main form, change it to

'-----------------code------------------------------
Private Sub YourCommandButtonName_Click()
Me.schedule.Form.Filter = "SomeDate Between #" & Me.StartDate & "# AND #" &
Me.EndDate & "#"
Me.schedule.Form.FitlerOn = True
End Sub
'--------------end of code-----------------------

Watch for word wrap in your browser.

Hi AccessVandal,

[quoted text clipped - 28 lines]

I have to say thank you to everyone who has given there input.

 

[AccessVandal via AccessMonster.com]

Is there a need to have a popup form after filtering the subform?

Why can't you just filter them at once?

I can't understand the rational of filtering sequence in this way. Can you
tell us what are the columns/fields of the table that you want to filter?

You had shown us the "WorkDate" (assumption only) which according to the code
that will work.

Am I to assume that you copied the code without editing the fields/columns to
match your table? You must replace "SomeDate" to the field/column of your
table. There should not be a popup "SomeDate" as Access can't find the
field/column of the table. Can I guess "WorkDate" is the field/column, which
is the one you wish to filter?

If it is, it should look like…(in one line, watch for word wrap in your
browser)

Me.schedule.Form.Filter = "WorkDate Between #" & Me.StartDate & "# AND #" &
Me.EndDate & "#"

If you're trying to filter another field/column of the table, why no just
combine two of them in one go. (in one single line….again watch for word wrap)


Me.schedule.Form.Filter = "WorkDate Between #" & Me.StartDate & "# AND #" &
Me.EndDate & "#" & " AND AnotherDateSomeWhere Between #" & Me.
AnotherDateControl1 & "# AND #" & Me.AnotherDateControl2 & "#"

Watch for spaces, you need to correct the syntax if necessary.

Note:
1. "AnotherDateControl" (unbound) you may need to create two more of these in
your form.
2. "AnotherDateSomeWhere" is the field/column name in your table. Replace it
to the correct name.

 

[Chris75 via AccessMonster.com]

Hi,

The field that I want to filter is called WorkDate. What worked earlier is
that when a work date was entered, the subform Schedule was filtered for that
work date. However, what I want to do is filter between a range of dates.
So I have the 2 unbound fields labelled StartDate and EndDate. I would like
the command button to filter between the dates in the above fields such that
all records that fall between the StartDate and the EndDate will appear in
the Schedule subform.

Is there a need to have a popup form after filtering the subform?

Why can't you just filter them at once?

I can't understand the rational of filtering sequence in this way. Can you
tell us what are the columns/fields of the table that you want to filter?

You had shown us the "WorkDate" (assumption only) which according to the code
that will work.

Am I to assume that you copied the code without editing the fields/columns to
match your table? You must replace "SomeDate" to the field/column of your
table. There should not be a popup "SomeDate" as Access can't find the
field/column of the table. Can I guess "WorkDate" is the field/column, which
is the one you wish to filter?

If it is, it should look like…(in one line, watch for word wrap in your
browser)

Me.schedule.Form.Filter = "WorkDate Between #" & Me.StartDate & "# AND #" &
Me.EndDate & "#"

If you're trying to filter another field/column of the table, why no just
combine two of them in one go. (in one single line….again watch for word wrap)

Me.schedule.Form.Filter = "WorkDate Between #" & Me.StartDate & "# AND #" &
Me.EndDate & "#" & " AND AnotherDateSomeWhere Between #" & Me.
AnotherDateControl1 & "# AND #" & Me.AnotherDateControl2 & "#"

Watch for spaces, you need to correct the syntax if necessary.

Note:
1. "AnotherDateControl" (unbound) you may need to create two more of these in
your form.
2. "AnotherDateSomeWhere" is the field/column name in your table. Replace it
to the correct name.

Hi Vandal,

[quoted text clipped - 5 lines]

Thanks!

 

[AccessVandal via AccessMonster.com]

Than the code I gave will work.

The filter is already searching your subform recordsource field "WorkDate"
between your input Date "StartDate and “EndDate". This is the input range of
date you wanted or is something else?

What worked earlier is that when a work date was entered, the subform
Schedule was filtered for that work date.

I don’t understand this part, why do you need to enter this control
"WorkDate" to search/filter when you already have "StartDate" and "EndDate"
(unbound controls) and if the control "WorkDate" is already bound to the
subform's recordsource. Why are you changing the data field of "WorkDate"? Or
it's just my misunderstanding that you’re using this control "WorkDate" to
filter your subform rather than the two unbound controls? Remember we can't
see your DB.

It is already filtering your "WorkDate" between the range base on your input
"StartDate" and "EndDate" so, what more do you wish to filter to?

 

[Chris75 via AccessMonster.com]

Hi,

I have my unbound fields on the main form. The command button is there as
well. The code is entered to filter records in subform labelled "Schedule".
It is possible that I inputed the code incorrectly. I entered dates into my
unbound fields (StartDate and EndDate). When I click on the button, a popup
opens asking me to enter "WorkDate". So I am guessing that somewhere along
the lines, I entered something incorrectly.

Than the code I gave will work.

The filter is already searching your subform recordsource field "WorkDate"
between your input Date "StartDate and “EndDate". This is the input range of
date you wanted or is something else?

What worked earlier is that when a work date was entered, the subform
Schedule was filtered for that work date.

I don’t understand this part, why do you need to enter this control
"WorkDate" to search/filter when you already have "StartDate" and "EndDate"
(unbound controls) and if the control "WorkDate" is already bound to the
subform's recordsource. Why are you changing the data field of "WorkDate"? Or
it's just my misunderstanding that you’re using this control "WorkDate" to
filter your subform rather than the two unbound controls? Remember we can't
see your DB.

It is already filtering your "WorkDate" between the range base on your input
"StartDate" and "EndDate" so, what more do you wish to filter to?

Hi,

[quoted text clipped - 5 lines]

all records that fall between the StartDate and the EndDate will appear in
the Schedule subform.

 

[Chris75 via AccessMonster.com]

Hi Vandal,

Got it to work. It was an extra space that caused the issue.

Thank you!!

Hi,

I have my unbound fields on the main form. The command button is there as
well. The code is entered to filter records in subform labelled "Schedule".
It is possible that I inputed the code incorrectly. I entered dates into my
unbound fields (StartDate and EndDate). When I click on the button, a popup
opens asking me to enter "WorkDate". So I am guessing that somewhere along
the lines, I entered something incorrectly.

Than the code I gave will work.

[quoted text clipped - 21 lines]

 

[AccessVandal via AccessMonster.com]

Glad to hear, Thanks for the feedback

Hi Vandal,

Got it to work. It was an extra space that caused the issue.

Thank you!!

Hi,

[quoted text clipped - 10 lines]