Automatically calculated date

[nx69]

I have a field called DateOfExpiration which i want to be automatically calculated according to the value of two other fields.

([TypesOfMemberships]="Bronze")=[DateAssigned]+91 Or ([TypesOfMemberships]="Silver")=[DateAssigned]+182 Or ([TypesOfMemberships]="Gold ")=[DateAssigned]+365

This is what i tried but it gives me an error promt of invalid SQL systax. Anyone who can help me?

thanks.

 

[Mile-O]

You'd find it easier if you had a table for membership types, something like:

tblMembershipType
MembershipTypeID
MembershipType
Duration

With this MembershipTypeID as a foreign key in your Membership table then you can add it to a query and calculate simply like this

DateOfExpiration: DateAdd("d", [Duration], [DateAssigned])

And, anyway, you shouldn't have a field for storing the expiry date as it violates Third Normal Form (3NF)

 

[nx69]

thanks

i already have a tblMemberships but the Duration field was in months. Anw thanks a lot.

Whats is 3NF?

I have to have an expiration date in order to detect expiring accounts.

 

[Mile-O]

i already have a tblMemberships but the Duration field was in months. Anw thanks a lot.

Whats is 3NF?

I have to have an expiration date in order to detect expiring accounts.

Third Normal Form is a rule of database normalisation. It states that you should not have a non-key dependency - basically a field that relies on others as these are calculable at any time in a query, form, or report.

i.e. you can make your expression work as a custom field within a query that does the calculation. On that field you can set the criteria you want for determing, for example, who's membership is expiring in the next week, month, etc.

Also, I suggested you move membership type to another table and not have it within your membership tables.

And months is probably a better measure for duration as by hard coding days into an expression as you were originally trying you open up two problems: actually having to go into the structure and edit should circumstances change, and the problem of leap years making calculations incorrect.

DateOfExpiration: DateAdd("m", [Duration], [DateAssigned])

If you do a search on normalization then you'll find the information on 1NF, and 2NF, too.

 

[nx69]

Thanks for the quick response but when I put the validation rule

DateOfExpiration: DateAdd("d", [Duration], [DateAssigned])

I get an error promt telling me: "The expression you entered has an invalid .(dot) or !operator or invalid parenthesis."

 

[Mile-O]

It's not a Validation Rule.

Have a look at this example I've made for you:

 

[nx69]

Now i see. Thanks.