Shoutbox

ok, im using access and in one table i have these 2 fields

LastUpdated and DateRegistered

now, ive made a 3rd field DaysLeftToRenew, the basic idea is that membership has to be renewed once a year, how can i display how many days that member has left to renew their membership in this field?

thanks

Hi,

You don't need an additional column in your database table for this -- you can compute it at any time, from what's already there. Use SQL functions datediff(), dateadd() and getdate().  The query might look something like this:

select DateRegistered, LastUpdated, datediff('d', getdate(), dateadd('yyyy', 1, LastUpdated)) as DaysLeftToRenew from MyTable

The above would evaluate to the difference (in days) between the current date and LastUpdated + one year, which sounds like what you want. 

SQL is a surprisingly powerful language, with many useful functions that allow you to use derived fields. If you must have an actual field though, then just set up a cron job to daily update that field.

quote:

---

If you must have an actual field though, then just set up a cron job to daily update that field.

---

If he wants to make it look like a table with a field, he can always create a view including the computed column... 

CREATE OR REPLACE VIEW MembershipDetails AS
SELECT LastUpdated, DateRegistered, datediff('d', getdate(), dateadd('yyyy', 1, LastUpdated)) as DaysLeftToRenew
FROM MemberList;

Should do the trick