date query in the sub-report

Hi,

I was wondering if i could get some help with SQL query. I have a report and
a sub-report. They both are linked by studentID field. My question is if i
want to perform a query to retrieve all records associated with that
studentID but with the month less than the current month. For example, in
master report (Departments Report), it uses a Departments table and in the
sub-report, it uses Students table. In the Students table, there is a field
called Entry Date with the following format: dd/mm/yyyy.

So if the current month is March or 03, the sub-report will show all the
student details with their entry date less than this month. I know that i
need to use something like Date(month) or something, so that it knows that
this month is March, it needs to output all records before March. How do i do
this in the sub-report (e.g. myStudents-SubReport). But how do i put in the
date(month) thing in the sub-report?

Any helps would be greatly appreciated.

Thank you in advance

This will return the first day of the current month:
DateSerial(year(Date()), month(Date()), 1)
...so, in the criteria cell of your date field:
DateSerial(year(Date()), month(Date()), 1)

HTH - Bob

Associates wrote:
Hi,

I was wondering if i could get some help with SQL query. I have a report and
a sub-report. They both are linked by studentID field. My question is if i
want to perform a query to retrieve all records associated with that
studentID but with the month less than the current month. For example, in
master report (Departments Report), it uses a Departments table and in the
sub-report, it uses Students table. In the Students table, there is a field
called Entry Date with the following format: dd/mm/yyyy.

So if the current month is March or 03, the sub-report will show all the
student details with their entry date less than this month. I know that i
need to use something like Date(month) or something, so that it knows that
this month is March, it needs to output all records before March. How do i do
this in the sub-report (e.g. myStudents-SubReport). But how do i put in the
date(month) thing in the sub-report?

Any helps would be greatly appreciated.

Thank you in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200904/1

It works really well, Bob.

Thank you

"raskew via AccessMonster.com" wrote:

This will return the first day of the current month:
DateSerial(year(Date()), month(Date()), 1)
...so, in the criteria cell of your date field:
DateSerial(year(Date()), month(Date()), 1)

HTH - Bob

Associates wrote:
Hi,

I was wondering if i could get some help with SQL query. I have a report and
a sub-report. They both are linked by studentID field. My question is if i
want to perform a query to retrieve all records associated with that
studentID but with the month less than the current month. For example, in
master report (Departments Report), it uses a Departments table and in the
sub-report, it uses Students table. In the Students table, there is a field
called Entry Date with the following format: dd/mm/yyyy.

So if the current month is March or 03, the sub-report will show all the
student details with their entry date less than this month. I know that i
need to use something like Date(month) or something, so that it knows that
this month is March, it needs to output all records before March. How do i do
this in the sub-report (e.g. myStudents-SubReport). But how do i put in the
date(month) thing in the sub-report?

Any helps would be greatly appreciated.

Thank you in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200904/1