Access Expression (Access 97)

Topic

New Reply

I’m trying to alter a field in an existing database so the person entering data can choose 1 of 3 options, and in another field a date is calculated and inserted. My current database does this with 2 options…the person chooses 6 months or 1 year, and the date 6 months or 1 year beyond is inserted in another field. Here is my current code to do this: PaidThrough: IIf([Member Type]=”6 months”,DateAdd(“m”,6,[Last Paid]),DateAdd(“m”,12,[Last Paid])).
Now I’m adding a 3rd option – 1 month – and I’m not having any success. Here is the closest I’ve come: PaidThrough: IIf([Member Type]=”1 month”,DateAdd(“m”,1,[Last Paid]) or If[Member Type]=”6 months”,DateAdd(“m”,6,[Last Paid]),DateAdd(“m”,12,[Last Paid])) but it says my operators and operands aren’t in order. I’ve tried many different Ifs, elses, or options and none have worked. Any suggestions? I’m thinking I’m missing something simple.

Reply | Quote

Replies

Hi Deebock,

Try the following expression instead:

IIf([Member Type]=”1 month”,DateAdd(“m”,1,[Last Paid]),IIf([Member Type]=”6 months”,DateAdd(“m”,6,[Last Paid]),DateAdd(“m”,12,[Last Paid])))

or

DateAdd(“m”,IIf([Member Type]=”1 month”,1,IIf([Member Type]=”6 months”,6,12)),[LastPaid])

Reply | Quote

Thanks Hans…your first expression did the trick!

Reply | Quote

>>Any suggestions? I’m thinking I’m missing something simple.<<

I have a suggestion. Don't store that calculated date in your database! This is a clear violation of database normalization rules. Why not change your MemberType field to an integer (dropping the "months"). Then whenever you need to calculate the PaidThru value, you can do a simple calculation: DateAdd("m",[MemberType],[LastPaid])

Reply | Quote

Mark,
We’re wondering why you feel this is a violation of database normalization? The field we’re calculating is the expiration date of the member, calculated by what type of member they are and when they paid their membership fee. Only one date is stored at a time and it changes each time they renew their membership. Comments?

Reply | Quote

Do you have the date paid stored? What Mark is talking about is that if you have the date paid stored as well, the storing the expiration date as well is unnecessary because it can always be calculated. If you store only the expiration date, then it is not a violation of normalization.

Reply | Quote

I see what you mean… Thanks for the refresher on normalization rules.

Reply | Quote

It’s easy to overlook the rules, mostly because it is easier to do the calculation on data entry and store the value, rather than having to do it every time. And we all have done this at one time or another for various reasons (some good, some bad). But you just need to recognize when you are storing such calculated data, even if you then make a conscientious decision to go ahead anyway. But keep it quiet, so the normalization police won’t come after you!

Reply | Quote

Because you are storing a value in a field that is derived solely from a calculation involving other fields in the record. The inherent risk is that you must remember to recalculate this field whenever ANY of the underlying fields get changed; and the problem is that fields don’t always get changed via a single form that you can control. There may be multiple forms, update queries, etc. Therefore, the PaidThrough date in your database may not always be correct, whereas deriving it when needed will ALWAYS be correct.

Reply | Quote

In a case like this wouldn’t it be a good idea to store the date so that if the calculation method does change it will not try to alter an already agreed expiration date?

Peter

Reply | Quote

I presume they have stored the DatePaid and the Months. I’m not sure what can change that would alter the calculation of the expiration date? If, for example, they change the number of months (let’s say everybody is getting a free month), then you want the expiration date to change automatically.

Reply | Quote

But maybe not the previous ones.

Reply | Quote

If in fact they are storing previous values, they should be in separate records. And you still calculate the DatePaidThru the same way for each record.

Reply | Quote