count days in query

  • Thread starter Thread starter SoggyCashew
  • Start date Start date
S

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;
 
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;
 
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


Ken Snell (MVP) said:
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>




SoggyCashew said:
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;
Click to expand...
Click to expand...
 
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!)


SoggyCashew said:
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


Ken Snell (MVP) said:
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>




SoggyCashew said:
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;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top