Using Month only in criteria

[Pitlochry1]

Is there a way of creating criteria for a whole month i.e January rather than
specifying Between >=#01/01/2009# And <=#31/01/2009# and having to put in
another criteria for every year?

And if so how would it look as a parameter?

 

[Allen Browne]

You want dates from a January of *any* year?

If your field is called SaleDate, type this in the Field row in query
design:
Month([SaleDate])
Then in the Criteria row under this, enter something like this:
[Month Number (1 to 12)]

It would also help to declare the parameter.
Choose Parameters on the Query menu.
Access opens a dialog.
Enter a row, using exactly the same name (preferably copy'n'paste):
[Month Number (1 to 12)] Long

 

[Pitlochry1]

Thanks Allen,

Yes *any* year was correct.

By the way the other Question you answered "Simplifying a Query with SQL
seems to have a problem opening.

Glenn

You want dates from a January of *any* year?

If your field is called SaleDate, type this in the Field row in query
design:
Month([SaleDate])
Then in the Criteria row under this, enter something like this:
[Month Number (1 to 12)]

It would also help to declare the parameter.
Choose Parameters on the Query menu.
Access opens a dialog.
Enter a row, using exactly the same name (preferably copy'n'paste):
[Month Number (1 to 12)] Long

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Is there a way of creating criteria for a whole month i.e January rather
than
specifying Between >=#01/01/2009# And <=#31/01/2009# and having to put in
another criteria for every year?

And if so how would it look as a parameter?

 

[Allen Browne]

By the way the other Question you answered "Simplifying a Query with SQL
seems to have a problem opening.

I'm not seeing any reply to my answer there. Post your answer again if you
described a problem, as i can't see it. (It happens.)

In case you are not able to see the reply I gave, I'm repeating it here:
======================================
In query design, enter something like this in the Criteria row under the
first date field:

= [StartDate] And < DateAdd("m", 1, [StartDate])

Below the Criteria row you will see several 'Or' rows.
On the first Or row under the next date field, enter the same expression.
On the next Or row under the next date field, enter it again.

To ensure Access understands the parameter correctly, declare it.
Choose Parameters on the Query menu.
Access opens a dialog.
Enter a row using exactly the same name, like this:
[StartDate] Date/Time

That should get you out of this spot, but the real problem is that you have
repeating fields. It would be better to create a related table where one
trainee can be assigned many dates, rather than have many date fields in
this table.