Prev: Ranking (tried a solution by John Spencer, but didn't work)
Next: Can you create a table from the output of a query?
From: Kay on 3 Jul 2008 18:36
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
From: Kay on 3 Jul 2008 18:46
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
From: Ken Snell (MVP) on 3 Jul 2008 19:19
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" <Kay(a)discussions.microsoft.com> wrote in message
news:F75BA283-0441-4CFA-AAA6-A321DB268493(a)microsoft.com...
> 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


 | 
Pages: 1
Prev: Ranking (tried a solution by John Spencer, but didn't work)
Next: Can you create a table from the output of a query?