Hi everyone, I'm fairly new to access and this is my first posting to this forum.

I'm trying to do something which I consider to be pretty straight forward but I'm stuck and would appreciate advice from those of you more experienced.

I have a date field in a table and from that date I work out a renewal date using the DateAdd field with a "yyyy" option.

In the query it says:

RenewalDate: DateAdd("yyyy",1,[DateDone])

where [DateDone] is held in a table.


From that I can obviously figure the annual anniversary of the event. What I would like to do next is to calculate how many days past (or before) the anniversary date is from the system date. I get that information by:

DaysDue: Date()-[RenewalDate]

If the value is a negative number, I know that it isn't yet due and a positive value is overdue.

Straight forward so far but the next piece is where I'm struggling. In the criteria row for the DaysDue field, I am putting in >=-30 so that I can find those renewals which are coming due in the coming month or which are already overdue but instead of giving me the results, the query prompts me for the field [RenewalDate].

So my question is this: can access not use derived fields with criteria in the same query in which they have been derived?

Any advice or information would be gratefully received.

Thanks

Peter

Quite right... You have to use the full formula/calculation in a field you want to put "where"-restrictions on.

ie
RenewalDate: DateAdd("yyyy",1,[DateDone])

DaysDue: Date()-DateAdd("yyyy",1,[DateDone])


Question though, this will work on 1 year aniversaries... what will happen on 2,3,4 etc years?

Ps Welcome to the forum

Instead of putting it in the criteria row, just calculate it directly with:

daysDue: (Date()-[renewalDate])>=-30

JR

EDIT: I haven't checked the cacluation, but my suggestion will give a true/false (0, -1), which is probably not what you wantet.