Date filter

[Chris75]

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

 

[Richard]

If your form is based on a query then use something like this in the criteria:

Between [Forms]![Schedule]![BeginDate] And [Forms]![Schedule]![EndDate]

it will filter between the two dates.

 

[Chris75]

Hi Richard,

The form is based on a table.

If your form is based on a query then use something like this in the criteria:

Between [Forms]![Schedule]![BeginDate] And [Forms]![Schedule]![EndDate]

it will filter between the two dates.

Hello,

[quoted text clipped - 12 lines]

Chris

 

[Arvin Meyer [MVP]]

Base your form on a query, instead of a table. The query should look like:

Select * from tblSchedule Where DateField Between Forms!Schedule![Enter
BeginDate] And Forms!Schedule![Enter EndDate];

 

[Chris75 via AccessMonster.com]

Hi Arvin,

If I based the form on a query instead of the table, would my form not become
read-only? I want to be able to enter schedules as well.


Thanks.

Sorry, that was wrong, it will prompt a second time. Instead use:

Select * from tblSchedule Where DateField Between Forms!Schedule![BeginDate]
And Forms!Schedule![EndDate];

and make sure you change the names of the table and date field to match
yours.

Base your form on a query, instead of a table. The query should look like:

[quoted text clipped - 20 lines]

 

[Chris75 via AccessMonster.com]

And remember that the fields BeginDate and EndDate are unbound and therefore
not part of my original table.

Hi Arvin,

If I based the form on a query instead of the table, would my form not become
read-only? I want to be able to enter schedules as well.

Thanks.

Sorry, that was wrong, it will prompt a second time. Instead use:

[quoted text clipped - 8 lines]

 

[John Spencer]

No forms do not become read-only just because they are based on a query.
As long as you update records in the query, you can update records
displayed on a form using that query.

And you apply the criteria against your date field.

I am not clear on whether or not you have a button or other method to
initiate a requery based on your entries in the two unbound controls.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

And remember that the fields BeginDate and EndDate are unbound and therefore
not part of my original table.

Hi Arvin,

If I based the form on a query instead of the table, would my form not become
read-only? I want to be able to enter schedules as well.

Thanks.

Sorry, that was wrong, it will prompt a second time. Instead use:

[quoted text clipped - 8 lines]

Chris

 

[John W. Vinson]

And remember that the fields BeginDate and EndDate are unbound and therefore
not part of my original table.

MOST forms are based on queries, and most queries are updateable. If you're
assuming that a query is ipso facto non-updateable you're missing a lot of the
power of Access!

Some queries - those for which Access cannot reliably tell which record to
update, such as Totals queries, UNION queries, complex multitable queries -
aren't updateable but a simple single-table query with a criterion or a sort
will be. And Arvin's suggested criteria are *from the form*, not from the
table.

 

[Chris75 via AccessMonster.com]

Hi John,

I don't have a button, but I have read up on the VBA behind setting it up...
let's just say that it didn't take. But still trying.

No forms do not become read-only just because they are based on a query.
As long as you update records in the query, you can update records
displayed on a form using that query.

And you apply the criteria against your date field.

I am not clear on whether or not you have a button or other method to
initiate a requery based on your entries in the two unbound controls.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

And remember that the fields BeginDate and EndDate are unbound and therefore
not part of my original table.

[quoted text clipped - 10 lines]

[quoted text clipped - 8 lines]
Chris

 

[Arvin Meyer [MVP]]

As long as you can edit the query, you will be able to edit the form.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

If I based the form on a query instead of the table, would my form not
become
read-only? I want to be able to enter schedules as well.


Thanks.

Sorry, that was wrong, it will prompt a second time. Instead use:

Select * from tblSchedule Where DateField Between
Forms!Schedule![BeginDate]
And Forms!Schedule![EndDate];

and make sure you change the names of the table and date field to match
yours.

Base your form on a query, instead of a table. The query should look
like:

[quoted text clipped - 20 lines]

Chris

 

[Chris75 via AccessMonster.com]

Thanks. One thing. I inputed your criteria and I get an invalid syntax
error.

Select * from tblSchedule Where DateField Between Forms!Schedule![BeginDate]
And Forms!Schedule![EndDate];

What is missing from the above? The fields and table names are the same.

As long as you can edit the query, you will be able to edit the form.

Hi Arvin,

[quoted text clipped - 18 lines]

 

[Arvin Meyer [MVP]]

Is the Schedule table name tblSchedule? How about the date field in the
table, is it named DateField?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks. One thing. I inputed your criteria and I get an invalid syntax
error.

Select * from tblSchedule Where DateField Between
Forms!Schedule![BeginDate]
And Forms!Schedule![EndDate];

What is missing from the above? The fields and table names are the same.

As long as you can edit the query, you will be able to edit the form.

Hi Arvin,

[quoted text clipped - 18 lines]

Chris

 

[Chris75 via AccessMonster.com]

The table name is jsut Schedule. The date field is just Date.

Is the Schedule table name tblSchedule? How about the date field in the
table, is it named DateField?

Thanks. One thing. I inputed your criteria and I get an invalid syntax
error.

[quoted text clipped - 11 lines]

 

[Douglas J. Steele]

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The table name is jsut Schedule. The date field is just Date.

Is the Schedule table name tblSchedule? How about the date field in the
table, is it named DateField?

Thanks. One thing. I inputed your criteria and I get an invalid syntax
error.

[quoted text clipped - 11 lines]

Chris

 

[Chris75 via AccessMonster.com]

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?

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]

 

[Douglas J. Steele]

If by "see what I was talking about" you mean you want to post a copy of
your database, the answer is no. Binary attachments are not desirable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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?

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

 

[Chris75 via AccessMonster.com]

Images included?

If by "see what I was talking about" you mean you want to post a copy of
your database, the answer is no. Binary attachments are not desirable.

Hi Douglas,

[quoted text clipped - 21 lines]

 

[Douglas J. Steele]

Images, being binary, are discouraged as well.

You're posting through AccessMonster, which may not have such rules, but all
AccessMonster is doing is interacting with an NNTP newsgroup. NNTP
newsgroups discourage binary attachments (unless the newsgroup's name
includes "binary" in it)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Images included?

If by "see what I was talking about" you mean you want to post a copy of
your database, the answer is no. Binary attachments are not desirable.

Hi Douglas,

[quoted text clipped - 21 lines]

Chris

 

[Arvin Meyer [MVP]]

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

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?

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

 

[AccessVandal via 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 & "#"