DateAdd Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that is being used for monthly evaluations for the boys in
our program. I need to be able to put the averages from the current month and
from the previous month. I am currently getting these numebers from two
queries. The first query calculates the current months evaluations. The month
and year are entered by the user to pull the report. The dates are pulled by
two date part fields: month and year. The second query takes the month and
subtracts 1 to get the previous months score. This has worked great until
this month.
How can I get it to recognize that 1 is the beginning of the year and 12 is
the end? From what I understand, I should be using a date add function
instead of the date part function. I don't understand how this works. Please
help.
 
It is hard to tell you how to write your query without knowing what your
table structure looks like. I'd also advise you to post your current query
so we can just modify it as we think is appropriate.

Dale
 
I have a database that is being used for monthly evaluations for the boys in
our program. I need to be able to put the averages from the current month and
from the previous month. I am currently getting these numebers from two
queries. The first query calculates the current months evaluations. The month
and year are entered by the user to pull the report. The dates are pulled by
two date part fields: month and year. The second query takes the month and
subtracts 1 to get the previous months score. This has worked great until
this month.
How can I get it to recognize that 1 is the beginning of the year and 12 is
the end? From what I understand, I should be using a date add function
instead of the date part function. I don't understand how this works. Please
help.
Click to expand...

You may want to actually store the data in a date/time field rather
than having separate fields for month and year. As requested - please
post the SQL. You may be doing a lot more work than necessary!

John W. Vinson[MVP]
 
Here is my SQL:
This month:
SELECT tblStudents.FirstName & " " & [LastName] AS Name,
DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate) AS [MONTH],
DatePart("m",[EvalDate]) AS EvalMonth, DatePart("yyyy",[EvalDate]) AS
EvalYear, tblStudents.StudentID
FROM tblStudents LEFT JOIN tblMain ON tblStudents.StudentID =
tblMain.StudentID
GROUP BY tblStudents.FirstName & " " & [LastName],
DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate),
DatePart("m",[EvalDate]), DatePart("yyyy",[EvalDate]), tblStudents.StudentID,
tblStudents.Active, tblStudents.StudentID
HAVING (((DatePart("m",[EvalDate]))=[Forms]![Switchboard].[cmbMonth]) AND
((DatePart("yyyy",[EvalDate]))=[Forms]![Switchboard].[cmbYear]) AND
((tblStudents.Active)=Yes))
ORDER BY DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate);

Last Month:
SELECT tblStudents.FirstName & " " & [LastName] AS Name,
DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate) AS [MONTH],
DatePart("m",[EvalDate]) AS EvalMonth, DatePart("yyyy",[EvalDate]) AS EvalYear
FROM tblStudents LEFT JOIN tblMain ON tblStudents.StudentID =
tblMain.StudentID
GROUP BY tblStudents.FirstName & " " & [LastName],
DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate),
DatePart("m",[EvalDate]), DatePart("yyyy",[EvalDate]), tblStudents.StudentID,
tblStudents.Active
HAVING (((DatePart("m",[EvalDate]))=([Forms].[Switchboard].[cmbMonth])-1)
AND ((DatePart("yyyy",[EvalDate]))=[Forms].[Switchboard].[cmbYear]) AND
((tblStudents.Active)=Yes))
ORDER BY DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate);

Here is where they are combined:
SELECT qryStudentAvgScoreByMonth.*, qryLastMonthStudenAvgScoreByMonth.*,
[Standard Deviation].*, qryAverageByMonth.*
FROM qryAverageByMonth INNER JOIN ((qryStudentAvgScoreByMonth INNER JOIN
qryLastMonthStudenAvgScoreByMonth ON
qryStudentAvgScoreByMonth.Name=qryLastMonthStudenAvgScoreByMonth.Name) INNER
JOIN [Standard Deviation] ON qryStudentAvgScoreByMonth.MONTH=[Standard
Deviation].MONTH) ON qryAverageByMonth.MONTH=qryStudentAvgScoreByMonth.MONTH;


I hope that helps clear things up.
Thank you for your help.
 
Here is my SQL:
Click to expand...

Try this instead:

SELECT tblStudents.FirstName & " " & [LastName] AS Name,
DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate) AS [MONTH],
DatePart("m",[EvalDate]) AS EvalMonth, DatePart("yyyy",[EvalDate]) AS
EvalYear, tblStudents.StudentID
FROM tblStudents LEFT JOIN tblMain ON tblStudents.StudentID =
tblMain.StudentID
GROUP BY tblStudents.FirstName & " " & [LastName],
DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate),
DatePart("m",[EvalDate]), DatePart("yyyy",[EvalDate]),
tblStudents.StudentID,
tblStudents.Active, tblStudents.StudentID
WHERE [EvalDate] >=
DateSerial([Forms]![Switchboard].[cmbYear],[Forms]![Switchboard].[cmbMonth],1)
AND [EvalDate] <
DateSerial([Forms]![Switchboard].[cmbYear],[Forms]![Switchboard].[cmbMonth]+1,1)
AND ((tblStudents.Active)=Yes))
ORDER BY DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate);

and for the previous month

SELECT tblStudents.FirstName & " " & [LastName] AS Name,
DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate) AS [MONTH],
DatePart("m",[EvalDate]) AS EvalMonth, DatePart("yyyy",[EvalDate]) AS
EvalYear, tblStudents.StudentID
FROM tblStudents LEFT JOIN tblMain ON tblStudents.StudentID =
tblMain.StudentID
GROUP BY tblStudents.FirstName & " " & [LastName],
DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate),
DatePart("m",[EvalDate]), DatePart("yyyy",[EvalDate]),
tblStudents.StudentID,
tblStudents.Active, tblStudents.StudentID
WHERE [EvalDate] >=
DateSerial([Forms]![Switchboard].[cmbYear],[Forms]![Switchboard].[cmbMonth]-1,1)
AND [EvalDate] <
DateSerial([Forms]![Switchboard].[cmbYear],[Forms]![Switchboard].[cmbMonth],1)
AND ((tblStudents.Active)=Yes))
ORDER BY DateDiff("m",tblStudents!EntranceDate,tblMain!EvalDate);


John W. Vinson[MVP]
 
Back
Top