Solved

Calculate number of days in query

Posted on 2011-09-13
4
341 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
[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
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36529336
<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
ID: 36529357
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
ID: 36529359
thank you
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 36529379
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

738 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