Record Count

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

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

Lets try that again.

"Kay" wrote:

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

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())

--

Ken Snell
MS ACCESS MVP



"Kay" wrote in message
...
Oh my... the formatting on my posting looks horrid and is unreadable...I
am
so sorry...

Lets try that again.

"Kay" wrote:

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