Create a report based on Month

[ryonker]

I have a table for tasked that have been done for a client. I need to run a report that lists all the tasks that were done in a specific month. I have been trying to find a way to set up a parameter query so that when I run the report a form would pop up and prompt me for the Month and the year. That would then pass the first and last day of that specific month to my date field in my query. Can anyone help me out with the coding. I have been searching for about 3 weeks and have not been able to find anything that would help.

Thanks, Robert

[Delerna]

Hi ryonker and welcome

Is there any reason you want a form to pop up?
I ask because you could put criteria into the query that the report is based off.

The criteria would be in the form of a field that does not exist.
That way, when the report is opened the query will ask the user for the value of the field that dosn't exist.

So, assuming you have a year field and a month field you would do this.

Open the query in design mode.
In the criteria for the year enter [Please Enter The Year]
In the criteria for the month enter [Please Enter The Year]

Hopefully you don't have fields in the query called [Please Enter The Year]
or [Please Enter The Year] :-)
This means the query, when run will popup two message boxes and the values that are entered will be used as the criteria

I hope this makes sense and is of assistance to you

[Delerna]

By the way, you can also do it with a form.

You would have a textbox for each criteria on the form where the user enters the values.
The query that the report is based off would then link to those textboxes in the "criteria" for the relevant fields.

The easiest way to link to the forms textboxes is like this

1) Open the query in design mode
2) Put the cursor in the criteria for the field you want to link to a forms textbox
3) On the toolbar at the top of the query window, click the wand icon. tooltip is Build
4) Open the forms folder
5) Open the all forms folder
6) Click the form that has the textbox you want to link
7) In the middle pane double click the textbox you want to link


You should finish up with something like this in the criteria for the field

Forms![YourForm]![YourTextbox]
 

You may or may not require the text property. I can't remember off hand
Like this

Forms![YourForm]![YourTextbox]Text 
 

Experiment until you get it right. Putting the link to the textbox into a spare field so that it appears in the queries result when you run it will help you to see if its right or not.

[ryonker]

By the way, you can also do it with a form.

You would have a textbox for each criteria on the form where the user enters the values.
The query that the report is based off would then link to those textboxes in the "criteria" for the relevant fields.

The easiest way to link to the forms textboxes is like this

1) Open the query in design mode
2) Put the cursor in the criteria for the field you want to link to a forms textbox
3) On the toolbar at the top of the query window, click the wand icon. tooltip is Build
4) Open the forms folder
5) Open the all forms folder
6) Click the form that has the textbox you want to link
7) In the middle pane double click the textbox you want to link


You should finish up with something like this in the criteria for the field

Forms![YourForm]![YourTextbox]
 

You may or may not require the text property. I can't remember off hand
Like this

Forms![YourForm]![YourTextbox]Text 
 

Experiment until you get it right. Putting the link to the textbox into a spare field so that it appears in the queries result when you run it will help you to see if its right or not.

Hi Delerna,

Thanks for the quick responses. I did it with the examples that you gave and have been able to get it to work correctly. Ideally what I would like to do is to have the user run the monthly tasks report. When it starts it would prompt them for a month that they would like the data from. The would then select February and the query would run and pull all the data that has a start date of 02/01/08 - 02/29/08. This is where I am getting lost. I know that the criteria in the query for the start date will have to begin with Between but I don't know how to specify that when the user selects February it should find the first day of February and the last day of February and then give me everything that is within those two dates.

I hope that this makes sense to you, its getting late and I have been staring at a monitor for a very long time and everything is starting to look the same.

Thanks for your help already and any more that you have.

Robert

[Delerna]

I will need an answer to a couple of questions in order to assist you further.

When the user selects a month Is it always going to be the current year?
The field that is to be used for the criteria is it a proper date field?

If the above are true you could use between.
But an easier way would be to check for records where the month of the date field = the month number entered by the user AND the year of the date field = the current year

so assuming the date field is called dte then where clause of the query will be something like

WHERE month(Dte)=Forms![YourForm]![YourTextbox]Text AND year(dte)=year(now())
 

if you prefer to use between(), say so and I will help you get it right

[ryonker]

I will need an answer to a couple of questions in order to assist you further.

When the user selects a month Is it always going to be the current year?
The field that is to be used for the criteria is it a proper date field?

If the above are true you could use between.
But an easier way would be to check for records where the month of the date field = the month number entered by the user AND the year of the date field = the current year

so assuming the date field is called dte then where clause of the query will be something like

WHERE month(Dte)=Forms![YourForm]![YourTextbox]Text AND year(dte)=year(now())
 

if you prefer to use between(), say so and I will help you get it right

I tried you suggestion and I get an invalid syntax error (You may have entered an operand without an operator and it highlights TEXT before the AND.

In answer to your questions the field that is used for my criteria is a proper date field (##/##/####). The box where the user will select the date is a combo box that list the month in text (January, February...) Somehow I need to find a way to translate the word February to (Between 02/01/08 and 02/29/08) for example or if there is a different way with not using the Between function. Does this make sense?
Robert

[Delerna]

Can you open the query in design mode and go to SQL view and copy the SQL Code and post it here.

[ryonker]

Can you open the query in design mode and go to SQL view and copy the SQL Code and post it here.

Here is the code:

 SELECT Tasks.Title, Tasks.[Active Status], Tasks.Priority, Tasks.Category, Tasks.Status, Tasks.[% Complete], Tasks.Description, Tasks.[Start Date]
FROM Tasks
WHERE (((Tasks.[Start Date]) Between DateSerial(Year(Date()),Month([Forms]![Form1]![cboMonth]),1) And DateSerial(Year(Date()),Month([Forms]![Form1]![cboMonth]),ReturnLastDateOfMonth(DateSerial(Year(Date()),Month([Forms]![Form1]![cboMonth]),1)))));
 

I also have a module with the following code:

 
Public Function ReturnLastDateOfMonth(datDate As Date)
    ReturnLastDateOfMonth = DateSerial(Year(datDate), Month(datDate) + 1, 0)
End Function
 

Hope that this is helpfull.

[n8kindt]

i dont mean to throw a wrench in your thoughts here, but i had a similar situation and i handled it this way...
(enjoy the picture illustration) :)

i made a table called months and created a recordset for each month with an ID that corresponded with the month number

i used that as the record source for the first unbound combo box you see in my form. (as a cool little trick, notice i set the default date to automatically bring up the current month)

and finally, this is how i set up my query.

this worked like a charm for me. hopefully this well help u get going in the right direction.

cheers,
nate

[Delerna]

If you have done as nate suggested with the combo box, which I assumed you had then you query would be

SELECT Tasks.Title, Tasks.[Active Status], Tasks.Priority, Tasks.Category, Tasks.Status, Tasks.[% Complete], Tasks.Description, Tasks.[Start Date]
FROM Tasks
WHERE year(Tasks.[Start Date]) = Year(now()) and month(Tasks.[Start Date])=[Forms]![Form1]![cboMonth]);
 

Also, nates query is just as legitimate. There is 1001 ways to skin a cat as the saying goes
By the way nate, cool post, a picture is worth a thousand words

[n8kindt]

If you have done as nate suggested with the combo box, which I assumed you had then you query would be

SELECT Tasks.Title, Tasks.[Active Status], Tasks.Priority, Tasks.Category, Tasks.Status, Tasks.[% Complete], Tasks.Description, Tasks.[Start Date]
FROM Tasks
WHERE year(Tasks.[Start Date]) = Year(now()) and month(Tasks.[Start Date])=[Forms]![Form1]![cboMonth]);
 

Also, nates query is just as legitimate. There is 1001 ways to skin a cat as the saying goes
By the way nate, cool post, a picture is worth a thousand words

thanks, delerna. and 1001 might be a little bit short ;-)

[ryonker]

thanks, delerna. and 1001 might be a little bit short ;-)

Thanks for the ideas. I am going to try these now and will let you know if I have success. Thanks for all the help up to this point.