Generating reports based on date

[iamdamien]

Hi,
I have generated a series of reports that show financial info, sales, payments made etc. And I would like to be able to print select months payments. All payments and sales are assigned a date already. Just wondered how I could do this. Like allowing the user to select a month; or search for payments made for a month etc.
Thanks.
D.

 

[Access_guy49]

create a query with your desired fields.. and one calculated field..
The calculated field will read something like this:
Expr1: Format([Name of Date field], "MM")

This will pull only the MONTH number from the records... so in your search criteria, you will put in a number.. or paramater for the user to input a number...or pass the paramater based on a form value. regardless... a value of 1 would how all January records... keep in mind... it would retrieve ALLLLLL january records. from ANY year.
If you want to get more specific, you will need to do the same thing for year and pass a year value as well.

 

[iamdamien]

Thanks. I will give it a try shortly. How could I link this to a textbox so that the user has complete control of what is printed? In a month/year format?

 

[Access_guy49]

Create a dropdown box of months.. and another of years. Then in your query you reference those controls. You may want to look at the "Build" option when you right click on the field name... it's helpful

 

[Access_guy49]

To create a drop down box of years, you might want to set the datasource using a query builder, select the date field, but use the format(datefield, "YYYY") setting so you only get years... then use the SELECT DISTINCT opening in SQL and you'll get a dropdown list of all the years in your database.

Does that make sense or do you need a better explination (I know that one is pretty junky..)

 

[iamdamien]

er. Maybe a nicer explanation would be brilliant! I'm sort of new to Access, but I have found that techniques are quite easy to pick up once I've got my head round it.

 

[Access_guy49]

ok.. so you make your drop down boxes right? right. You don't set the control source because your not saving the data in the dropdown, your simply using it as a tool.
So you set the row source.. (The SOURCE of the Rows used in the drop down.. )

When you click on the three little dots on the right, you get a query builder pop up. this query builder is a quick way to build the SQL statement the dropdown list will use to populate it's values. Start with a basic Query.. just select a table.. and any field. then close it off, save the form and open it and look at what the list was populated with.

Now go back and edit the query, what you want is the years...
So you are going to pick your table and your date field. In the "Field" line of the query, you are going to change it so that it reads
Year: Format([your date field name here], "YYYY")

And your going to set it to sort ascending or descending, whichever you wish.

Then close and save, and take a look at what you get.
so that's your drop down list...

hmmm.. I think a sample database might explain better... be back with that later today.

 

[Access_guy49]

Try this example out.. look at it for a bit, play around, i think you'll get it.
In queries.. when I set the criteria to be a value based on a form's control.. I always use the right click and BUILD option.. then you look for Access > Forms > All Forms > form name your want > then the control you are looking for..
That way i know i get the right syntax and proper spelling of the name.
Hope this helps
Feel free to ask any questions if you don't get something.

 

[iamdamien]

Right I have had a look, and I sort of understand what is going on. I can populate my combobox with dates. But how do I remove the duplicate years from the selection? So say I want to show payments from Jan 2011 for instance? The combobox can only show the year, which leads to 10 selections of "2011" populating the combobox!

 

[iamdamien]

I think I have fixed the issue now. Just need to make sure that the reports are generated from the selection of the month.

 

[Access_guy49]

Right I have had a look, and I sort of understand what is going on. I can populate my combobox with dates. But how do I remove the duplicate years from the selection? So say I want to show payments from Jan 2011 for instance? The combobox can only show the year, which leads to 10 selections of "2011" populating the combobox!

To remove duplication of values in a combobox make sure your RowSource for that combobox starts with "SELECT DISTINCT"
Is you only see SELECT and then blah blah blah... just add DISTINCT after the word SELECT and that should fix that issue.

 

[iamdamien]

To remove duplication of values in a combobox make sure your RowSource for that combobox starts with "SELECT DISTINCT"
Is you only see SELECT and then blah blah blah... just add DISTINCT after the word SELECT and that should fix that issue.

Yeah thanks. I had this issue for a while, until I realised I had incorrectly typed DISTINCT as DISTINT. Schoolboy errroR!
Think everything is all good now.
Thanks for the help.

 

[iamdamien]

Is it possible to generate a report with the filter of the financial yearend? So for instance a report that when a user selects a yearend eg. 2011, the report generates all information from April 2010 to April 2011? Can this be done with only one input from the user? In the form of a combobox where only the yearend is selected?