Advanced Filter on a Date field

[Guest]

There is a field in my table that contains dates.

I would like to use the Advanced Filter feature to see only those records
with a date in a given month.

So far I have been unsuccessful in figuring out the syntax for the criteria.

The Month() function takes a parameter of type date and returns the month
number, but I don't know how to say "use the date in the current field".

Month(*)=7 and Month(this)=7 both give me syntax errors.

 

[John Vinson]

There is a field in my table that contains dates.

I would like to use the Advanced Filter feature to see only those records
with a date in a given month.

So far I have been unsuccessful in figuring out the syntax for the criteria.

The Month() function takes a parameter of type date and returns the month
number, but I don't know how to say "use the date in the current field".

Month(*)=7 and Month(this)=7 both give me syntax errors.

Rather than using Advanced Filter, why not just use a Query?

Create a Query based on the table. Three options for a freely selected
month:

- put in a calculated field Month([datefieldname]) and use a criterion

=[Enter month number:]

Note that this will give you results from February 1993, February 2004
or February 2187 if you enter 2 in the criterion and the table
contains dates in those months.

- Put in a criterion on the datefield of

= DateSerial([Enter year:], [Enter month number:], 1) AND <DateSerial([Enter year:], [Enter month number:]+1, 1)

- Put in a criterion

BETWEEN [Enter start date, mm/dd/yyyy:] AND [Enter end date:]

for full flexibility.

John W. Vinson[MVP]

 

[Guest]

Thank you for your reply John. However, my question is regarding doing this
using the advanced filter feature. Do you know if it can be done?

There is a field in my table that contains dates.

I would like to use the Advanced Filter feature to see only those records
with a date in a given month.

So far I have been unsuccessful in figuring out the syntax for the criteria.

The Month() function takes a parameter of type date and returns the month
number, but I don't know how to say "use the date in the current field".

Month(*)=7 and Month(this)=7 both give me syntax errors.

Rather than using Advanced Filter, why not just use a Query?

Create a Query based on the table. Three options for a freely selected
month:

- put in a calculated field Month([datefieldname]) and use a criterion

=[Enter month number:]

Note that this will give you results from February 1993, February 2004
or February 2187 if you enter 2 in the criterion and the table
contains dates in those months.

- Put in a criterion on the datefield of

= DateSerial([Enter year:], [Enter month number:], 1) AND <DateSerial([Enter year:], [Enter month number:]+1, 1)

- Put in a criterion

BETWEEN [Enter start date, mm/dd/yyyy:] AND [Enter end date:]

for full flexibility.

John W. Vinson[MVP]

 

[Allen Browne]

Tom, see my reply in the queries group, where you can me the same response.

We do prefer that you not post the same question multiple times to different
groups. If you do need to post to multiple groups, post a single message
with both groups nominated in the one message, so all the threads appear
together.

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

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

Thank you for your reply John. However, my question is regarding doing
this
using the advanced filter feature. Do you know if it can be done?

There is a field in my table that contains dates.

I would like to use the Advanced Filter feature to see only those
records
with a date in a given month.

So far I have been unsuccessful in figuring out the syntax for the
criteria.

The Month() function takes a parameter of type date and returns the
month
number, but I don't know how to say "use the date in the current field".

Month(*)=7 and Month(this)=7 both give me syntax errors.

Rather than using Advanced Filter, why not just use a Query?

Create a Query based on the table. Three options for a freely selected
month:

- put in a calculated field Month([datefieldname]) and use a criterion

=[Enter month number:]

Note that this will give you results from February 1993, February 2004
or February 2187 if you enter 2 in the criterion and the table
contains dates in those months.

- Put in a criterion on the datefield of

= DateSerial([Enter year:], [Enter month number:], 1) AND
<DateSerial([Enter year:], [Enter month number:]+1, 1)

- Put in a criterion

BETWEEN [Enter start date, mm/dd/yyyy:] AND [Enter end date:]

for full flexibility.

John W. Vinson[MVP]