Query by month

[Maglore]

i got a field that have Dates in it
is there a way to get a report by month ??

 

[FoFa]

For a specific month:
Where Month([DateField]) = SomeInputParameter

Or by all months:
Group By Year([DateField]), Month([DateField])

But the last one will force to do something with any returned column in your query, either add it to the group by, or return an aggregate.

Depends on what you are looking for.

 

[Maglore]

hello, i just got a question cuz im kinda new to there. where i put that code, in a query in the field date ?? and the thing with, someinputparameter, what can i put there ?

 

[FoFa]

For a specific date, if you want to process only one month, you need to specify the month (and usually the year so you don't get say April 2004 with April 2005). How you do that depends on what you want. The easiest is to create a query. Create two alias columns, one for year and one for month using your date field in the table I will call MyDate. It would be like this:
CheckYear: YEAR([MyDate])
CheckMoth: MONTH({MyDate])

Then in the criteria section of the query for CheckYear put this: [Enter Year wanted]
Then in the criteria section of the query for CheckMonth put this: [Enter Month wanted]

Once that is complete run it and test it.
Once it works, you can create a report using the report wizard from that query.

 

[raskew]

A similar method, but which only requires response to one parameter, is to add a calculated field, Expr1: format([myDateField], "mm/yyyy") and set the criteria to [Enter mm/yyyy]. This will return only records corresponding to the month/year specified.

HTH - Bob

 

[Maglore]

i think im dumb,
what do i write in the [MyDateField] cuz is tells me that its nor a valid function
so, i put instead the name of the query ??

 

[ColinEssex]

[MyDateField] is the name of the field that holds the date you are looking for

Col

 

[manishgoel]

Sir,
I have table containing date field & Corresponding sale figure in sales column.
I want to design a querry cabaple of cumulating sales figure in next column