Date filter

Just go into SQL view and copy the text and paste it in a news message. Most
of us can read SQL directly, we may see your problem.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Chris75 via AccessMonster.com" <u54211@uwe> wrote in message
news:9b160d65268ce@uwe...
> Hi Douglas,
>
> I had just finished reading one of your other posts regarding this. I
> will
> change the field name and seet what it gives me.
>
> Question:
>
> Would it work best if you could actually see what I was talking about
> rather
> than me describing it?
>
> Douglas J. Steele wrote:
>>Consider renaming your field. Date is a reserved word, and should never be
>>used as a field name.
>>
>>For a comprehensive list of names to avoid (as well as a link to a free
>>utility to check your application for compliance), see what Allen Browne
>>has
>>at http://www.allenbrowne.com/AppIssueBadWord.html
>>
>>> The table name is jsut Schedule. The date field is just Date.
>>>
>>[quoted text clipped - 5 lines]
>>>>>>>>>>
>>>>>>>>>> Chris
>
> --
> Message posted via http://www.accessmonster.com
>

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 & "#"

Chris75 wrote:
>Hello,
>
>I've gone through various articles and posts to try to figure this out...with
>no success. I am using Access 2007.
>
>Okay. I have a form, with the requisite unbound text boxes for Begin Date
>and End Date (BeginDate and EndDate). What I obviously want to do is filter
>my records based on those 2 controls. Here's where I am unclear. I have
>read about the WHERE query and the sFilter Code. Do I do both or one or
>should I just write a macro filter, which is something I have not done at all?
>
>My form is called Schedule.
>
>Thanks for your help.
>
>Chris

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200908/1

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.




AccessVandal wrote:
>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
>

--
Message posted via http://www.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 wrote:
>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.

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200909/1

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!

AccessVandal wrote:
>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.
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200909/1

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 wrote:
>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!

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200909/1