Selected Month Report

[mkkashif]

i want to make a query which give the selective month data.
Means if enter in parameter march it gives all records of the march
i have form in which i have a combo for all months .
january to december.

i want when i click of march the report should only show the record of the month march or any month in the combobox.how can i do that .in query or report.

 

[Ally]

There's a variety of ways you can do this.

In a query, under the date field, you'll need to put in the Criteria row,

Between [Enter Start Date] and [Enter End Date]

Therefore when your query is run, for March, you would enter 01/03/04 (or 03/01/04 depending on whether your settings are US or UK) and in the second box, 31/03/04.

If you just wanted to enter a month at a time you can format your date to accept a single month.

In the Field row of your query, you would put (an example):

Mnth: Format([YourDateField], "yyyy/mm")

and under this in the criteria row:

[Enter Month as yyyy/mm]

Therefore, when you run query, you would enter 2004/03 for March.

(I have found that putting the year first, helps when you want to sort by year, although other members may have some other options).

Hope this helps. Post back if it's not clear or you need more help.

 

[mkkashif]

Please See my AttachMent

See my attach file.
in that i have a sample table.and sample and a query .i want when the user click on search button the query shows the results of that month.
any help greatly appreciated

 

[mkkashif]

Month Vise Report

what critaria i have to put in the query .
see my attachment.
i need some code for my attachment.
thanks

 

[mkkashif]

Can somone help

can some one help on my thread.

 

[Ally]

Please be patient. We mostly all have other work to do.

If you're going to do it with a drop down box, you need to get rid of the month table. It has no relevance to your other tables.

Also, you shouldn't use names like 'date', 'month', 'year' etc as field names as they confuse Access as they are built in names for functions, therefore you need to change the date field to another name - in this example I used, SmpDate.

In your drop-down box on the form, change the Row Source to the Sample table. You can then make a query from that table to pull out the month from the date field. For ease, just copy and paste the following into the Row Source on the properties of your form.

SELECT Format([SmpDate],"mm") AS Mth FROM Sample GROUP BY Format([SmpDate],"mm");

If you click the ellipsis (...) at the end of Row Source it will take you to the query itself and you can see what I've done with it.

Then you need to change your query itself:

In the date field, put:

Mth: Format([SmpDate],"mm")

In the criteria row of this field put:

[Forms]![SampleForm]![Combo2]

Basically, this is formatting your dates to just months as I explained in the first post, then looking up just the months.

 

[mkkashif]

Thanks For your help

it's working thanks for your help.

 

[nescartel]

mkkashif,

can u show me how u solved this problem?