Record Count

[Kay]

This is a field in my query

Field Eligibilty Date: DateAdd("m",7,[Start Date])
Table
Total Expression
Sort
Show Yes
Criteria
Format mm/yy

that returns the following example correctly:

Start Date Eligibilty Date
01/01/2008 07/08


But what I dont know how to set the criteria to pull only the current months
records of eligibilty. I have tried adding two additional fields that looked
like below,

Field month([eligibilty date])
year([eligibilty date])
Table
Total Expression
Expression
Sort
Show Yes Yes
Criteria Month(Now())
Year(Now())

However all I get is a prompt to enter the paremater value of "eligibilty
date"

My ultimate goal is to have a form that displays a count of how many records
contain the current mm/yy in the "elgibilty date" field.

Anyone have any ideas?

Thanks in advance

Kay

 

[Kay]

Oh my... the formatting on my posting looks horrid and is unreadable...I am
so sorry...

Lets try that again.

:

This is a field in my query

Field Eligibilty Date: DateAdd("m",7,[Start Date])
Table
Total Expression
Sort
Show Yes
Criteria
Format mm/yy

that returns the following example correctly:

Start Date Eligibilty Date
01/01/2008 07/08

But what I dont know how to set the criteria to pull only the current months
of eligibilty. I have tried adding two additional fields that looked
like below,

Field month([eligibilty date])
Table
Total Expression
Show Yes
Criteria Month(Now())

Field Year([eligibilty date])
Table
Total Expression
Show Yes
Criteria Year(Now())



However all I get is a prompt to enter the paremater value of "eligibilty
"date"

My ultimate goal is to have a form that displays a count of how many records
contain the current mm/yy in the "elgibilty date" field.

Anyone have any ideas?

Thanks in advance

Kay

 

[Ken Snell \(MVP\)]

You need to repeat the original expression in the two new expressions:

Field month(DateAdd("m",7,[Start Date]))
Table
Total Expression
Show Yes
Criteria Month(Now())

Field Year(DateAdd("m",7,[Start Date]))
Table
Total Expression
Show Yes
Criteria Year(Now())