Form filter on date

[Ticotion]

Hi

I have a form that is based on a table where the user inputs data. The table
consist of rows (of cause) where the first attribute is a date (dd/mm/yyyy).
When the user opens the form it should only show data for the this year
(sysdate). How should this be done?

Thank you

Ticotion

 

[Marshall Barton]

I have a form that is based on a table where the user inputs data. The table
consist of rows (of cause) where the first attribute is a date (dd/mm/yyyy).
When the user opens the form it should only show data for the this year
(sysdate).

Not sure that's what you really want to do in early January.

You can use a query as the form's record source. Set the
date field's criteria to something like:

 

[Ticotion]

Not sure that's what you really want to do in early January.

You can use a query as the form's record source. Set the
date field's criteria to something like:
>=DateSerial(Year(Date()), 1, 1)

Hi

If I use a query as the form records source how will the user then be able
to input data directly into the table?

Is it not possible to make a VBA code that filters the records I want when
opening the form?

regards

Ticotion

 

[Marshall Barton]

If I use a query as the form records source how will the user then be able
to input data directly into the table?

Is it not possible to make a VBA code that filters the records I want when
opening the form?

A query is a virtual table so you can do pretty much the
same things you do with a table. In addition you can sort,
filter and aggregate data in a query. Queries are the life
blood of databases and in fact are the **only** way records
can be sorted. Tables are just barrels that records are
tossed into and pulled out of

When you open a table in datasheet view, Access creates
its own internal query to help you out (or get in the way,
depending on your point of view). Access does the same kind
of "helpful" things when you use the fluffy menu and toolbar
stuff to manipulate records.

It is way more efficient to filter the data users need to
work with before loading it into a form than it is to load
all the data and then filter it. This is such a basic idea
that many/most applications open to an unbound form where
users can enter the filtering criteria and after the
criteria is specified open the filtered data form.

Back to your specific question, yes it's possible to filter
a form's recordset using the form's Filter property. The
Filter property works in most simple situations so you could
get away with it for now. Since you want the forn to be
filtered when it first opens, the code would go in the
form's Open (or Load) event procedure:

Me.Filter = "[date field name]>=DateSerial(Year(Date()),1,1)
Me.FilterOn = True

If you are sure you only want data for the current year,
(nothing on Jan 1 and a ton in December), you can use this
slightly simpler filter:

Me.Filter = "Year([date field name])=Year(Date())"
Me.FilterOn = True

 

[Ticotion]

Thank you for your answer. I see your point regarding that the user only will
be able to see very few data in the beginning of the year. So if your want to
see current month - 2 instead, how should you then use the dateserial
function?

thank you for your help

ticotion

If I use a query as the form records source how will the user then be able
to input data directly into the table?

Is it not possible to make a VBA code that filters the records I want when
opening the form?

A query is a virtual table so you can do pretty much the
same things you do with a table. In addition you can sort,
filter and aggregate data in a query. Queries are the life
blood of databases and in fact are the **only** way records
can be sorted. Tables are just barrels that records are
tossed into and pulled out of

When you open a table in datasheet view, Access creates
its own internal query to help you out (or get in the way,
depending on your point of view). Access does the same kind
of "helpful" things when you use the fluffy menu and toolbar
stuff to manipulate records.

It is way more efficient to filter the data users need to
work with before loading it into a form than it is to load
all the data and then filter it. This is such a basic idea
that many/most applications open to an unbound form where
users can enter the filtering criteria and after the
criteria is specified open the filtered data form.

Back to your specific question, yes it's possible to filter
a form's recordset using the form's Filter property. The
Filter property works in most simple situations so you could
get away with it for now. Since you want the forn to be
filtered when it first opens, the code would go in the
form's Open (or Load) event procedure:

Me.Filter = "[date field name]>=DateSerial(Year(Date()),1,1)
Me.FilterOn = True

If you are sure you only want data for the current year,
(nothing on Jan 1 and a ton in December), you can use this
slightly simpler filter:

Me.Filter = "Year([date field name])=Year(Date())"
Me.FilterOn = True

 

[Marshall Barton]

Thank you for your answer. I see your point regarding that the user only will
be able to see very few data in the beginning of the year. So if your want to
see current month - 2 instead, how should you then use the dateserial
function?

Sorry to be so long getting back to this, but I have been
really tied up.


The last couple of months can be filtered with: