asked on

How do I use a formula that countdowns to a certain date (in Microsoft Access 2003) and then run a query that finds all less than 30 dates?

In my Access database (named Audit Issues) I have a field named Days to Target Date. I need to run some kind of math formula that will calculate and countdown how many days from the current date to the target date. For example, if the target date is January 1, 2010 something that will countdown until that date so if I am entering data today (July 13, 2009) the calculation will have 172 days appear.

Further, I need to learn how to run a query so that I can sort my table and eventually run a report that finds all data entries in this field (Days to Target Date) that are less than 30 days until target date and less than 60 days until target date. First, how do I go about running a query and second how do I run/use one under these two conditions?

I am a beginner to Microsoft Access (using version 2003), so detailed and step by step procedures/directions would be greatly appreciated. Thank you very much for all of your help, Experts Exchange has been great so far.

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeToole

You should not store the 'number of days to target' as a table column - its value obviously changes from day to day and would therefore require to be updated each day. Instead store the Target date in the table and calculate the days-to-target whenever it needs to be shown:

Select Field1, Field2, ..., datediff("d", Date(), TargetDate) as DaysToTarget From MyTable

For the 30, 60 day selection, add a Where clause:

Select Field1, Field2, ..., datediff("d", Date(), TargetDate) as DaysToTarget From MyTable
Where datediff("d", Date(), TargetDate) <=30

MaeMcG

ASKER

Hi MikeToole - I need to have it as a column and then update it accordingly each time (as frustrating as that is) -- do you know how I would go about doing that? Thanks very much!

MikeToole

What do you need the column for? Any access to the data that requires the Days to Target value should be via a a Select statement that calculates it - that's how it's done.
What database are you using?

Kevin Cross

I already showed and explained this all yesterday -- http:#24844132.

MikeToole

Sorry, yes your initial post said Access - I was looking at the URL for the Question which just said SQL_Syntax.
But the question still remains: Why does 'Days to Target' need to be physically stored as a column in the table? I can't think of any reason to design it that way.
If it is an absolute requirement then you're going to need a scheduled daily batch job to maintain the column.