Solved

Calculate number of days in query

Posted on 2011-09-13
4
336 Views
Last Modified: 2012-05-12
Experts,
Here a query I'm developing that calculates a future date based upon a date in my table.
I would like to be able to subtract maturityDate from dateID to show the number of actual days.
One final thing. Will the query as presented take into account leap years? If not, how does one handle leap years.

SELECT tblDate.DateID, DateSerial(Year([dateID]),Month([dateID]),Day([DateID])+30) AS maturityDate
FROM tblDate;
0
Comment
Question by:Frank Freese
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
<Will the query as presented take into account leap years? > Yes

<I would like to be able to subtract maturityDate from dateID to show the number of actual days>

the actual days difference is already in your query (30)

anyway

SELECT tblDate.DateID, DateSerial(Year([dateID]),Month([dateID]),Day([DateID])+30) AS maturityDate, DateDiff("d",[DateID],[maturityDate]) as NDays
FROM tblDate
0
 

Author Comment

by:Frank Freese
Comment Utility
thanks cap: I know my actual days are in the query, but my boss wants to see the Access calculation to reassure himself the query is right.
0
 

Author Closing Comment

by:Frank Freese
Comment Utility
thank you
0
 
LVL 15

Expert Comment

by:OMC2000
Comment Utility
maturityDate was defined in the same statement and cannot be used here as operand of select expression. And why we need functions here I always use direct operations and it used to work

SELECT tblDate.DateID, DateID+30 AS maturityDate, SomeDateField - SomeOtherDateField as NDays
FROM tblDate
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now