count days in query

[SoggyCashew]

hello, I have a query that I want to count how many days its been since
"Excused Absence" was entered. each time its entered I use the InputDate for
the date it was entered. Here is the SQL im using without what im asking...
Thanks!

SELECT Year([inputdate]) AS [Year], tblInput.InputDate, tblInput.InputText,
tblInput.UserID, Month([InputDate]) AS SortKey, tblInput.AttendanceReason
FROM tblInput
WHERE (((Year([inputdate]))=[Forms]![frmCalendar].[CalYear]) AND
((tblInput.InputText)="Excused Absence") AND
((tblInput.UserID)=[forms]![frmCalendar]![cboUser]))
ORDER BY Month([InputDate]) DESC;

 

[Ken Snell \(MVP\)]

If I'm understanding your question correctly,

SELECT Year([inputdate]) AS [Year], tblInput.InputDate, tblInput.InputText,
tblInput.UserID, Month([InputDate]) AS SortKey, tblInput.AttendanceReason,
DateDiff("d", inputdate, Date()) & " days" AS HowLongHasItBeen
FROM tblInput
WHERE (((Year([inputdate]))=[Forms]![frmCalendar].[CalYear]) AND
((tblInput.InputText)="Excused Absence") AND
((tblInput.UserID)=[forms]![frmCalendar]![cboUser]))
ORDER BY Month([InputDate]) DESC;

 

[SoggyCashew]

Yes thats what I wanted, thanks! How could I get it to say the total in days,
Months? Like if it was 36days how could I get it to say 1 month 5 days. Or
could I convert the days from the query on my Report? Thanks!
--
Thanks,
Chad

If I'm understanding your question correctly,

SELECT Year([inputdate]) AS [Year], tblInput.InputDate, tblInput.InputText,
tblInput.UserID, Month([InputDate]) AS SortKey, tblInput.AttendanceReason,
DateDiff("d", inputdate, Date()) & " days" AS HowLongHasItBeen
FROM tblInput
WHERE (((Year([inputdate]))=[Forms]![frmCalendar].[CalYear]) AND
((tblInput.InputText)="Excused Absence") AND
((tblInput.UserID)=[forms]![frmCalendar]![cboUser]))
ORDER BY Month([InputDate]) DESC;

--

Ken Snell
<MS ACCESS MVP>

hello, I have a query that I want to count how many days its been since
"Excused Absence" was entered. each time its entered I use the InputDate
for
the date it was entered. Here is the SQL im using without what im
asking...
Thanks!

SELECT Year([inputdate]) AS [Year], tblInput.InputDate,
tblInput.InputText,
tblInput.UserID, Month([InputDate]) AS SortKey, tblInput.AttendanceReason
FROM tblInput
WHERE (((Year([inputdate]))=[Forms]![frmCalendar].[CalYear]) AND
((tblInput.InputText)="Excused Absence") AND
((tblInput.UserID)=[forms]![frmCalendar]![cboUser]))
ORDER BY Month([InputDate]) DESC;

 

[Douglas J. Steele]

Try replacing the DateDiff function with the Diff2Dates function at
http://www.accessmvp.com/DJSteele/Diff2Dates.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes thats what I wanted, thanks! How could I get it to say the total in
days,
Months? Like if it was 36days how could I get it to say 1 month 5 days. Or
could I convert the days from the query on my Report? Thanks!
--
Thanks,
Chad

If I'm understanding your question correctly,

SELECT Year([inputdate]) AS [Year], tblInput.InputDate,
tblInput.InputText,
tblInput.UserID, Month([InputDate]) AS SortKey,
tblInput.AttendanceReason,
DateDiff("d", inputdate, Date()) & " days" AS HowLongHasItBeen
FROM tblInput
WHERE (((Year([inputdate]))=[Forms]![frmCalendar].[CalYear]) AND
((tblInput.InputText)="Excused Absence") AND
((tblInput.UserID)=[forms]![frmCalendar]![cboUser]))
ORDER BY Month([InputDate]) DESC;

--

Ken Snell
<MS ACCESS MVP>

hello, I have a query that I want to count how many days its been since
"Excused Absence" was entered. each time its entered I use the
InputDate
for
the date it was entered. Here is the SQL im using without what im
asking...
Thanks!

SELECT Year([inputdate]) AS [Year], tblInput.InputDate,
tblInput.InputText,
tblInput.UserID, Month([InputDate]) AS SortKey,
tblInput.AttendanceReason
FROM tblInput
WHERE (((Year([inputdate]))=[Forms]![frmCalendar].[CalYear]) AND
((tblInput.InputText)="Excused Absence") AND
((tblInput.UserID)=[forms]![frmCalendar]![cboUser]))
ORDER BY Month([InputDate]) DESC;