Question Calculations

[Hey Lucy]

Let me start by saying I do not know how to use SQL or VB so all I am doing is within Access itself.

I have a Membership Database created in Access. The main table, Members Table, includes a field for Next Renewal. The Next Renewal Date should be one year from the last membership update. To date, the end user has been putting the Next Renewal in manually. What I want to do is to have it auto-entered by the program by writing a calculation that basically says =([LastUpdate]+365 and automatically posts the answer into the Next Renewal field.

Now, I know I cannot do calculations in a table, but I can in a form. So I wrote the calculation in the form, but when entries are made on the form that should update in the table, the Next Renewal field shows blank in the table.

I need that field in the table because I have to run a query based on the Next Renewal and I can't run a query based on a form field.

Can what I wish to do be done? How would I go about it?

 

[Alansidman]

Why not create a field in a query and put a calculation in the new field as an expression.

Renewal Date: [LastUpdate]+365

Then apply your criteria to the new field.

 

[Brianwarnock]

If you want to add a year add a year not 365 days, use Dateadd

Brian

 

[Alansidman]

Excellent Point Brian. DateAdd slipped my mind. That is a more accurate solution and avoids issues with leap years.

 

[Hey Lucy]

Thanks for all the great responses. I created an expression in a query, but the problem now is that the field I created the expression in does not recognize my criteria?

 

[RainLover]

Please Post your Expression.

 

[Hey Lucy]

My expressions is as follows:

=IIF([RenewalDate]>0, [RenewalDate]+365, IIF([RenewalDate], "", [OriginalMembershipDate]+365))


My critieria is to find all the ones who need to renew within a certain month, as we send out monthly solicitations to those who are expired at that time. My criteria is as follows (so the end user only has to put in the dates):

>=[Enter First Date] and <=[Enter Last Date]

So, for example, the end user would supply the following responses:

04/01/2012 and 04/30/2012

Thanks so much for your help!

 

[RainLover]

=IIF([RenewalDate]>0, [RenewalDate]+365, IIF([RenewalDate], "", [OriginalMembershipDate]+365))

Change to.

=IIF([RenewalDate], DateAdd('y',1,[RenewalDate]), DateAdd('y',1,[OriginalMembershipDate]))

Untested

If this does not work check DateAdd in Access Help.

 

[Hey Lucy]

Thanks so much for your response, Rain. I used your expression and it works wonderfully, although I can't query any criteria based on it. What I did was create a query with that expression that makes a table, then I created a 2nd query based on the query-created table and was able to query my criteria for the correct results! Also, I did have to change 'y' in the expression to 'yyyy' because I use 4-digit year.

Anyway, this solved the problem and now I have it all down to a simple menu button that runs everything, gets the criteria results, then exports to excel (what the user prefers as opposed to an Access report) and all the end user has to do is select the button on the menu. Presto!!!

Thanks again. I'm so glad I stumbled upon the forum. You guys are the best. I'm sure I'll be asking a lot of questions in the future.

 

[RainLover]

Glad you got an a solution.