Calculated field to extract a 14-day period and check if today's date falls within?

[tim_tims33]

Hi,



I've got a members table where my members pay an annual fee. The fee is paid 12 months on from when they originally registered. So, for example, 12 months from today, i would expect to pay my next annual fee.

Now, with different members registering at different times of the year, it isn't so straightforward. I would like my calculated field to indicate to me if a payment is required.

Furthermore, I would like to include a '' date range '' so that the calculated field entitled ''Payment Required?'' shows yes for a fortnight. Here is my attempt at the expression which partially works:

PaymentRequired?: IIf((Format(Date(),"dd/mm")>= [RenewalDate] And Format(Date(),"dd/mm")<=[RenewalDateAdd14])Or ([Subscription Paid?]=0),"Yes","No")

As you can see, i had to get rid of the year...so i used the Format Date method. But i've got a case where someone is due to pay at the end of November...and today being teh 3rd of December...they should have a good few days yet to pay the fee (before teh aforementioned 14 days is up)

Does anyone know why this isn't working?

Thanks

tim

[ssanfu]

You didn't "get rid of the year". All format does is change how the date is displayed.

Is [RenewalDate] is a text field? What does the data look like?

To get rid of the year, you would need to do something like:

PaymentRequired?: IIF (Day(Date()) & "/" & Month(Date())>= [RenewalDate] AND .........


BTW, you should only use letters, numbers and the underscore in object names.

[tim_tims33]

Yes...thank you for that advice...i had a strong feeling that the 'format' function didn't get rid of the year.

RenewableDate should be a 'date field' (...eventhough it is a calculated field). I have attached a screen dump to show you what it looks like.

I am using the function DateAdd to add 14 days onto the original Renewal Date. It now looks like this (see attachment)

RenewalDateEnd (original post entitled 'RenewalDateAdd14: DateAdd("d",12,(Day([RenewalDate]))&"/"&(Month([RenewalDate]))

This still provides me with a date of the form: dd/mm/yyyy ... where my hope is to achieve dd/mm (just 14 days on from RenewalDate.

A reminder that the whole purpose here is to identify if today's date is between RenewalDate and RenewalDateEnd (originally called RenewalDateAdd14).

I'm playing around with the recommendations in your last post...but to no avail yet!

Thanks....please advise further if you can.

tim

[ssanfu]

Try this query: (I took out the spaces and the special characters)

SELECT tblMembers.SubscriptionPaid, tblMembers.RenewalDate, DateSerial(Year(Date()),Month([renewaldate]),Day([renewaldate])) AS RenewalThisYr, DateAdd("d",14,[RenewalThisYr]) AS RenewalDateAdd14, (Date() Between [renewaldate] And [renewaldateadd14]) And Not [SubscriptionPaid] AS PaymentRequired
FROM tblMembers;