Query Problems using DateDiff function 2

[DawsonUK]

Hi, I'm currently working at The Robert Gordon University developing a database for holding information about students, and am having difficulty creating a specific query.

Basically, Students study many Modules, and for each Module submit many Courseworks. Each coursework submission contains a date of when it was submitted amounngst other things. There is also a field called "feedback sent". The query I am creating is based upon when "feedback sent" = No.

I want to display a list of all courseworks currently "in the system", (feedback not sent), listing the amount of days each one has been in the system, in a Descending order (highest first).

I understand to do this I would be looking to use the DateDiff Function, on the field "Date Submitted", and using the Date() function as todays date. However, I'm admittedly a newbie in SQL.

Any help would be greatfully appreciated, and I could email my database to anyone if that would make the problem easier to understand.

Thanks,
David

 

[zrzr]

To retrieve the number of days between a date and today's date, add a column to your query as an expression.
In this column, put :

DateDiff("d",Date(), [Date Submitted])

the "d" is to say you want the number of day

Is it what you were looking for ?

 

[DawsonUK]

Hi, not quite, I was trying to figure out how to get the query to display that date in the results, as every time I add a column in the query say called "Days_difference" with that criteria, upon running the query it prompts me to enter "Days_difference"

Thats the problem I'm trying to work around, and I'm sure its probably a simple one too.

Thanks,
David

 

[DanChard]

Hi there. I'm not a big fan of the datediff function (basically because I've never had to use it and therefore - don't know how to!) so my solution might not be the best but it should work:

select *, int(now()-date_submitted) as time_in_system
from coursework_submissions
where [feedback sent] = "No"
order by int(now()-date_submitted) desc

This will select everything from the "coursework_submissions" table (obviously you'll have to change this to the name of the table you're using for this purpose) plus the amount of days spent in system - this is based on the "date_submitted" column - where no feedback has been sent (feedback sent = "No&quot.

Hope this all makes sense. Please post again if you need any more help.

Good luck!

 

[DawsonUK]

Hi, thanks, I never realised that the criteria for the variable days_in_system should be in the field name, thats where I was going wrong!

Now, It shows the correct amount of days, and all the required information, but to be a pain, the days should be displayed in weeks, which I have done by diving by 7, but this leaves loads of decimal places, any suggestions as how to get rid of these?

The SQL code I have right now that works is below (slightly long winded)....

SELECT Students.[Full Name], Students.Specialism, [Assignment Submissions].[Date Submitted], [Assignment Submissions].[Feedback Sent], Courses.Name, [Assignment Questions].[Assignment Title], (DateDiff("d",[Date Submitted],Date()))/7 AS weeks_in_system
FROM [Assignment Questions] INNER JOIN (Courses RIGHT JOIN (Students RIGHT JOIN (Grades RIGHT JOIN [Assignment Submissions] ON Grades.[Grade ID] = [Assignment Submissions].[Grade ID]) ON Students.[Student ID] = Grades.[Student ID]) ON Courses.[Course ID] = Students.Course) ON [Assignment Questions].[Assignment ID] = [Assignment Submissions].Assignment
GROUP BY Students.[Full Name], Students.Specialism, [Assignment Submissions].[Date Submitted], [Assignment Submissions].[Feedback Sent], Courses.Name, [Assignment Questions].[Assignment Title]
HAVING ((([Assignment Submissions].[Feedback Sent])=No));

Thanks,
David

 

[DawsonUK]

Hi, thanks again for the help. Never realised I could right click and choose properties and Choose the number of decimal places.

Thanks,
David

 

[zrzr]

Did you know dateDiff allow to get the number of week without having to calculate it ?

You just have to repace "d" by "ww", and it's done !!!

The result is the same, but it should be a bit faster ....

 

[DawsonUK]

I knew that in the depths of my mind, but never thought about using it! Thanks again!

David

 

[TnGranny]

FYI ON DATEDIFF FUNCTION:

If the result is over 999, it returns 0.

Workaround is

Format(DateDiff("d",[BegDate],[EndDate]),"0000&quot

This is in Office XP. I don't believe I used datediff in previous version of Access so it may be okay in them.