Solved

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?

Posted on 2009-07-13
6
262 Views
Last Modified: 2012-05-07
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.
0
Comment
Question by:MaeMcG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24844132
Firstly, you can use the DateDiff function provided with MS Access to determine the days difference from today (can use Date() function to get that) and date specified in the future.

SELECT DATEDIFF("d", DATE(), #2010-01-01#);

If you have been storing this data all along into your field of how many days until the target date, you can just do your query like this:

SELECT *
FROM your_table
WHERE days_til_target < 30;

--OR

SELECT *
FROM your_table
WHERE days_til_target < 60;

If you want to calculate this on data that already exists with say a date field, you can use the DateDiff function again like this.

SELECT *
FROM your_table
WHERE DATEDIFF("d", date_field, #2010-01-01#) < 30;

Hopefully that is clear and helps.

Best regards,
Kevin
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24848883
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
0
 

Author Comment

by:MaeMcG
ID: 24849336
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!
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 27

Expert Comment

by:MikeToole
ID: 24849496
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?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24850179
I already showed and explained this all yesterday -- http:#24844132.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24850622
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySql Workbench outputting "OK" as result of queries. 5 50
T-sql Date Format 9 66
SQL Rewrite without the NULLIF 4 33
Finding Where Clause Value in SQL Views and SP 21 62
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question