automatically store an expiry date (1 Viewer)

[shutzy]

i need a method to store an expiry date. i have a column prepared in my table but i do not know how to reach the desired result.

the date will always be different depending on certain criteria

the course length. some courses we have are valid for a month. some for 6 months. some for a year and some for only 14 days.

i can use the date add to add the date period to the purchase date but i do not know where or how to store course length to be able to use it.

i have approached this before and i had a column in my courses table as below

("m",3,Date())

i pland on haveing this type for each course and varying it as applicable but this did not work and apparantley i was breaching the fundamental rules of relational data.

do how do i do it.

i am open to all suggetions.

thank you

 

[David R]

Included in the definition of a course should be the duration/relative expiration. So it goes in that table, UNLESS different offerings of a course (Summer, Intensive, etc) have different expirations. Then it goes in the Offering table, probably.

Can you summarize what the "certain criteria" are that define duration?

 

[shutzy]

there is no definition of what define duration of the course. a course doent really have a duration but an expiry date. ie if a client has not used all or the course item by 'x' date then the course has expired anyway.

i am looking for a way to say not this cannot be used as a course item as it has already expired.

for this i need to know the expiry date

which is

date purchased + time permitted to use course in full = expirey date

the date purchased is easy Date()
the dateadd function is also easy but how to get it in there without an error is hard.

as per my example before i need some way to tell the dateadd function to add x days or x months.

 

[David R]

If there truly is no pattern, then yes, store your expiration date with each course/client. But if there is, you should store the 'definition' of an valid course in some way. Exactly how you do it is up to you, but it sounds like you might have a lookup table that says "two weeks";14;"three months";91, etc. There are a lot of options, but then your expiry date is just DateEnrolled+Duration, no DateAdd() necessary (though you can use it).

Alternatively you can make it store a number and a unit (months, weeks, etc) but that's probably overkill in this situation.

 

[shutzy]

hmmm. i thought the only way to get a date from a calculation was by using date add. how do i write the calculation.

ie. 01/01/2013 + 3 months would be?

thanks for your insite so far. very useful

 

[namliam]

Well a date is basicaly a number...
3 months exactly is difficult but there are different ways of calculating with dates

i.e.
+30 is a month
+91 is 3 months
+182 is half a year
+365 is a year

They do not always match up with "reality" but its close enough and generaly accepted I believe. And you can play with the numbers a little bit ... depending on your requirements.

If you need it to be as exact as possible... You would indeed need to use dateadd (or something along simular lines) to do "M", +6 for 6 months

 

[shutzy]

thanks for your help.

i have managed to do it now. i have tried something similar to this but i wasnt successful. i think you saying that it can be done spared me on to complete it thanks.

the images below show how i did it.

 

[David R]

Glad to hear it!

 

[namliam]

Just from the layout ... the ExpiryValue seems to be a text field, seeing as you should only have numbers there.... it seems like a bad idea.