Sort by month

[T13v0hz]

Is it possible to have a select query that looks up by the month selected?
ie. in a parameter query.
Should I make a lookup query that has a list of months because access always needs to have a full date not just a month or year?

or

Should I make 12(!) querys, one for each month? What do I do when I get to next year?

 

[RichO]

Do you want to query all records for a specific month, regardless of year?

You can use an expression field in a query to isolate the month:

=DatePart("m", [YourDateField])

 

[T13v0hz]

I want to be able to select the jobs for one month by selecting the month not the date (currently "Between [Enter start date] And [Enter end date]").
The year is important. Is it possible to say 'y = now' or something similar? I don't want last years "April" coming up with this years.

I have already isolated it with the format mmmm, but it still asks for a full date not just a month.

Could I make a macro for it??

 

[RichO]

This is easy to do with a query.

Set up your query with the desired fields. Then add 2 new columns and in the field boxes, enter the expressions:

=Month([YourDateField])
=Year([YourDateField])

Then you'll need to specify the criteria. If you want only dates from the current year, for year enter:

Year(Date())

If the desired month is going to selected from a form, you'll need to put a reference to that form's control in the criteria box, like:

[Forms]![YourFormName]![YourControlName]

Whatever month number (1-12) you enter in the criteria for the month field, the query will return all records in that month of the current year.

 

[raskew]

Try placing this expression in the criteria cell
of the date you wish to filter by:

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

When prompted, enter something like 04/2004. The query will return all
records between the 1st and last day of the target month.

HTH-Bob

 

[T13v0hz]

Thankyou,

I'm sure this will help.

 

[elandro]

I've a similar doubt

I've different records about logins from different users on a same table. What I want to do is to sort by user and list the quantity of logins per month (from that user). I mean, discriminate the quantity of records of X month, listing them per month.

For example. User A

January 2003 23 (registries/logins)
July 2003 10
December 2003 25
February 2004 11

(the months and the years have not neccesarily be listed, but to have a guideline... link

23
10
25
11

then with vbscript on asp I add months.

 

[RichO]

Elandro,

You can do this with a report. Use the report wizard to create the report for you. You can select grouping levels so you can first group by user, then by month within each user grouping.

If you just want to see the month and the count of logins in that month, delete all of the controls in the detail section and close up the detail section. Then add 2 text boxes to the month group header. One for the month and one for the count of records in that month. Use =Count(*) as the control source for that text box.

 

[elandro]

The problem is that I'd need to display this on an asp page. With a query can you bring every registry or information from a report?

 

[RichO]

OK, call me stupid but what's an asp page?

 

[elandro]

it's a similar language as php (dynamic pages, programmed) but made by microsoft (where you use vbscript).

 

[RichO]

OK, that's beyond the scope of my knowledge. Sorry I can't help.