Report by Year

[sabrina30]

I have a report that is pulling data from a query. I figured out how to get the report to pull for just a specific month, but was told they would rather have the report pull by year with a breakdown in quarters. I think I have the quarter part figured out. I can not however, figure out what to put in the criteria of the query so that it asks for a 4 digit date.

This is the formula I found for pulling by month

Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1

Yes, I am fairly nooby at Access.. so please be gentle.

 

[ThomasLafferty]

Hi Sabrina!
Try this:
When prompted, why not just enter a year worth of dates:
(1/1/2006 12/31/2006). Use this for criteria:
Between DateValue([Enter start date dd/mm/yyyy]) And DateValue([Enter end date dd/mm/yyyy])

Hope this helps...
Tom

Live once die twice; live twice die once.

 

[THWatson]

sabrina30
Rather than using a parameter query, here is a more flexible approach.

1. Create an unbound form. Let's call it frmSelectDates.
2. On the form put two unbound text boxes. Let's call the one txtStartDate and the other txtEndDate.
3. On the form, also put a command button to drive the report. (use the command button wizard to set it up)
4. In the query colum that holds your dates, put the following criteria

Between Forms!frmSelectDates!txtStartDate And Forms!frmSelectDates!txtEndDate

Tom

 

[sabrina30]

Thank you THWatson, that worked. Thomas Lafferty thank you also for your input. I had a between dates query set up in the beginning, but it seemed to be pulling more then what I wanted. I would enter 01/01/06 - 06/12/06 and I would have dates from 2005.

Now to figure out how to get the Form to display rather than the parameter that states "Forms!frmSselectDates!tstStartDate".

Oh one more quick question. I keep getting the error that my report is wider than the page. I set the page to landscape, I reduced the size of the fields, I checked everything I could think of, I selected the control farthest to the right. The report currently is only 7.5 in width. Why am I getting this error?

 

[sabrina30]

I figured out at the report being wider than the page issue. Thank you.

How do I get the unbound form to display for the user to enter the dates for the report?

 

[ThomasLafferty]

You could use a switchboard with a button labeled "Yearly Report" or something appropriate.

To set up a switchboard, if you don't already have one, click Tools>Database Utilities>Switchboard Manaager. If you receive an error that there is no valid switchboard, answer Yes to create one. Click the Edit button, then click new to add a command. Under text, type "View Report." From the command dropdown, select open Form in edit mode. From the Form dropdown, select the name of your form: frmSelectDates. Click OK>Close>Close.

The switchboard is a nice, easy way to control user interaction with your database. You can add pages and commands anytime you want, and even cause it to display as your startup form. To do this, click Tools>Startup. From the Display Form/Page dropdown, select switchboard, or whatever you chose to name it. After you have built your database, it's not a bad idea to hide the database window which you can do from the same location (Tools>Startup), and depending on the level of your user(s), you might even want to consider disabling special keys. If you haven't done so, you can redisplay your database window anytime you want to by pressing the F11 key, or by holding down your shift key when launching your database. (Incidentally, this overrides your startup options and won't display your switchboard until you close and reopen your db.)

Hope this helps.


Tom

Live once die twice; live twice die once.