Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Calculate number of days in query

Posted on 2011-09-13
Medium Priority
346 Views
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
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
• 2

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 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>

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

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

ID: 36529359
thank you
0

LVL 15

Expert Comment

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

Question has a verified solution.

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