Using Month only in criteria

  • Thread starter Thread starter Pitlochry1
  • Start date Start date
P

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?
 
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
 
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

Allen Browne said:
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.

Pitlochry1 said:
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?
Click to expand...
Click to expand...
 
Pitlochry1 said:
By the way the other Question you answered "Simplifying a Query with SQL
seems to have a problem opening.
Click to expand...

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])
Click to expand...

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.
 
Back
Top