Date Range for Report

  • Thread starter Thread starter Kerrin @ Reception
  • Start date Start date
K

Kerrin @ Reception

I enter data on a daily basis for stationery taken from a cupboard.
At the end of the week i need to see what was taken over that whole week or
month.
How do i enable myself to enter a date range into my database to retrieve
these records?
 
Hello Kerrin,

You can create a Query having all necessary fields from your tables. Under
the dateField use the criteria

Between [Enter begining date] and [Enter endind date]

By opening this query you will be prompted the questions shound in square
brackets and all matching records will be shown.

You can use later this query to built a report.

Another way to filter your records is to have a from with two textboxes to
enter the begining and ending date, and have a command button to open your
query or report.

In this case you should change the criteria on your query to filter the
dates according to those textboxes, e.g. Between
Forms![YourFormName]![YourBeginingTxtBox] and
Forms![YourFormName]![YourEndingDateTxtBox].

Hope this helps,

GeorgeCY

Ο χÏήστης "Kerrin @ Reception" έγγÏαψε:
 
Hello Kerrin,

You can create a Query having all necessary fields from your tables. Under
the dateField use the criteria

Between [Enter begining date] and [Enter endind date]

By opening this query you will be prompted the questions shound in square
brackets and all matching records will be shown.

You can use later this query to built a report.

Another way to filter your records is to have a from with two textboxes to
enter the begining and ending date, and have a command button to open your
query or report.

In this case you should change the criteria on your query to filter the
dates according to those textboxes, e.g. Between
Forms![YourFormName]![YourBeginingTxtBox] and
Forms![YourFormName]![YourEndingDateTxtBox].

Hope this helps,

GeorgeCY

Ο χÏήστης "Kerrin @ Reception" έγγÏαψε:
 
I enter data on a daily basis for stationery taken from a cupboard.
At the end of the week i need to see what was taken over that whole week or
month.
How do i enable myself to enter a date range into my database to retrieve
these records?
Click to expand...

Do you always want just the current full month?
As criteria for the current full month:
Where Format([DateField],"mm yyyy") = Format(Date(), "mm yyyy")

For the current full week:
Where Format([DateField],"ww yyyy") = Format(Date(),"ww yyyy")

If you wish to pick any month, use:
Where Format([DateField],"mm yyyy") = Format([Enter any valid date in
that month], "mm yyyy")

To pick any week you wish, use:
Where Format([DateField],"ww yyyy") = Format([Enter Any valid date in
that week],"ww yyyy")

You will be prompted to enter a date.
Note... a valid date must include the month, day and year.
 
I enter data on a daily basis for stationery taken from a cupboard.
At the end of the week i need to see what was taken over that whole week or
month.
How do i enable myself to enter a date range into my database to retrieve
these records?
Click to expand...

Do you always want just the current full month?
As criteria for the current full month:
Where Format([DateField],"mm yyyy") = Format(Date(), "mm yyyy")

For the current full week:
Where Format([DateField],"ww yyyy") = Format(Date(),"ww yyyy")

If you wish to pick any month, use:
Where Format([DateField],"mm yyyy") = Format([Enter any valid date in
that month], "mm yyyy")

To pick any week you wish, use:
Where Format([DateField],"ww yyyy") = Format([Enter Any valid date in
that week],"ww yyyy")

You will be prompted to enter a date.
Note... a valid date must include the month, day and year.
 
On Tue, 26 May 2009 19:23:01 -0700, Kerrin @ Reception <Kerrin @
I enter data on a daily basis for stationery taken from a cupboard.
At the end of the week i need to see what was taken over that whole week or
month.
How do i enable myself to enter a date range into my database to retrieve
these records?
Click to expand...

Hi Kerrin,

George and Fred's techniques will work fine. Here's another approach
that has more flexibility in the long run, if you're interested in
some VBA coding.

You can have a report prompt the user for criteria before it runs by
opening a form from the report's Open event. Open the form in Dialog
mode so that the report waits for the form to be closed or hidden
before it proceeds. That way you can collect criteria from the user
and build a Where clause for the report. It also means that you can
call the report directly - you don't need to call it from a form. And
the selection form is reusable - it can be called from multiple
reports if they need the same criteria.

I've posted examples of this technique at
www.JStreetTech.com/downloads - see "Report Selection Techniques".

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
On Tue, 26 May 2009 19:23:01 -0700, Kerrin @ Reception <Kerrin @
I enter data on a daily basis for stationery taken from a cupboard.
At the end of the week i need to see what was taken over that whole week or
month.
How do i enable myself to enter a date range into my database to retrieve
these records?
Click to expand...

Hi Kerrin,

George and Fred's techniques will work fine. Here's another approach
that has more flexibility in the long run, if you're interested in
some VBA coding.

You can have a report prompt the user for criteria before it runs by
opening a form from the report's Open event. Open the form in Dialog
mode so that the report waits for the form to be closed or hidden
before it proceeds. That way you can collect criteria from the user
and build a Where clause for the report. It also means that you can
call the report directly - you don't need to call it from a form. And
the selection form is reusable - it can be called from multiple
reports if they need the same criteria.

I've posted examples of this technique at
www.JStreetTech.com/downloads - see "Report Selection Techniques".

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top