and will your dates span years?
and what about leap years?
use the code on this link.
http://www.iaccessworld.co
or
http://www.projectperfect.
two dates using an Access function
Upload sample data in an access database.PARAMETERS
DateStart DateTime,
DateEnd DateTime;
SELECT DISTINCT
10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor,
Year(DateAdd("m",[Factor],[DateStart])) AS [Year],
Month(DateAdd("m",[Factor],[DateStart])) AS [Month],
IIf(DateDiff("m",DateAdd("m",[Factor],[DateStart]),[DateEnd])=0,
Day([DateEnd]),Day(DateSerial(Year(DateAdd("m",[Factor],[DateStart])),Month(DateAdd("m",[Factor],[DateStart]))+1,0))
-IIf([Factor]=0,Day([DateStart])-1,0)) AS Days
FROM
msysobjects AS Uno,
msysobjects AS Deca
WHERE
(((10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10))<=DateDiff("m",[DateStart],[DateEnd])));
SELECT DISTINCT
Dates.Id,
Dates.DateStart,
Dates.DateEnd,
10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor,
Year(DateAdd("m",[Factor],[DateStart])) AS [Year],
Month(DateAdd("m",[Factor],[DateStart])) AS [Month],
IIf(DateDiff("m",DateAdd("m",[Factor],[DateStart]),[DateEnd])=0,
DateDiff("d",[DateStart],[DateEnd]),Day(DateSerial(Year(DateAdd("m",[Factor],[DateStart])),Month(DateAdd("m",[Factor],[DateStart]))+1,0))
-IIf([Factor]=0,Day([DateStart])-1,0)) AS Days
FROM
msysobjects AS Uno,
msysobjects AS Deca,
Dates
WHERE
(((10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10))<=DateDiff("m",[DateStart],[DateEnd])));
Note please, that I've corrected an error in the day count of the first month in those cases where both dates fall in the same month, and in the day count of the last month which was off by 1.SELECT DISTINCT
Dates.Id,
Dates.DateStart,
Dates.DateEnd,
10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor,
Year(DateAdd("m",[Factor],[DateStart])) AS [Year],
Month(DateAdd("m",[Factor],[DateStart])) AS [Month],
IIf(DateDiff("m",[DateStart],[DateEnd])=0,
DateDiff("d",[DateStart],[DateEnd]),
IIf(DateDiff("m",DateAdd("m",[Factor],[DateStart]),[DateEnd])=0,
Day([DateEnd])-1,
Day(DateSerial(Year(DateAdd("m",[Factor],[DateStart])),Month(DateAdd("m",[Factor],[DateStart]))+1,0))
-IIf([Factor]=0,Day([DateStart])-1,0))) AS
Days,
DateDiff("d",[DateStart],[DateEnd]) AS DaysTotal
FROM
msysobjects AS Uno,
msysobjects AS Deca,
Dates
WHERE
10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)<=DateDiff("m",[DateStart],[DateEnd]);
/gustav
SELECT DISTINCT
Dates.Id,
Dates.DateStart,
Dates.DateEnd,
DateAdd("d",1,Dates.[DateEnd]) As DateEndNext,
10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor,
Year(DateAdd("m",[Factor],[DateStart])) AS [Year],
Month(DateAdd("m",[Factor],[DateStart])) AS [Month],
IIf(DateDiff("m",[DateStart],DateAdd("d",1,[DateEnd]))=0,
DateDiff("d",[DateStart],DateAdd("d",1,[DateEnd])),
IIf(DateDiff("m",DateAdd("m",[Factor],[DateStart]),DateAdd("d",1,[DateEnd]))=0,
Day(DateAdd("d",1,[DateEnd]))-1,
Day(DateSerial(Year(DateAdd("m",[Factor],[DateStart])),Month(DateAdd("m",[Factor],[DateStart]))+1,0))
-IIf([Factor]=0,Day([DateStart])-1,0))) AS
Days,
DateDiff("d",[DateStart],DateAdd("d",1,[DateEnd])) AS DaysTotal
FROM
msysobjects AS Uno,
msysobjects AS Deca,
Dates
WHERE
10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)<=DateDiff("m",[DateStart],DateAdd("d",1,[DateEnd]));
/gustav
Title | # Comments | Views | Activity |
---|---|---|---|
How to convert sql code to access query | 8 | 53 | |
Export code - file path based on either at Home or Work | 5 | 21 | |
MS Access Query Sum Total IIF criteria IsNull Help Please | 7 | 14 | |
Double clicking on a button causes an issue | 7 | 0 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
10 Experts available now in Live!