One Query instead of Three?

  • Thread starter Thread starter Telesphore
  • Start date Start date
T

Telesphore

I have 3 queries: qry1 for rpt1, qry2 for rpt2, and qry3 for rpt3.

qry1 has January 1 2003 as criteria for the field [InscriptionDate]
qry2 has May 1 2003 as criteria for the field [InscriptionDate]
qry3 has September 1 2003 as criteria for the field [InscriptionDate]

The others fields are identical in each query.

Is it possible to have only one query for one report instead of 3 queries
for 3 different reports?

Also, I would like to convert automatically the dates of the query, since
the date in the form [Forms]![frmStudentChoice]![BulletinDate] is changed
during each term: January for Winter, May for Summer and September for Fall.

Thank you.
 
Telesphore said:
I have 3 queries: qry1 for rpt1, qry2 for rpt2, and qry3 for rpt3.

qry1 has January 1 2003 as criteria for the field [InscriptionDate]
qry2 has May 1 2003 as criteria for the field [InscriptionDate]
qry3 has September 1 2003 as criteria for the field [InscriptionDate]

The others fields are identical in each query.

Is it possible to have only one query for one report instead of 3 queries
for 3 different reports?
Click to expand...

Absolutely. You can use a criteria like:

=Forms!someform.sometextbox

so if you or the users set sometextbox on someform to the
desired date, then the query will be filtered to that date.

As long as you're going to use a form to enter the date, you
may as well make the arrangement even cleaner. Add a button
on the form to print the report. Then modify the code
behind the button to use the OpenReport method's
WhereCondition argument. This way the query does not need
to have any criteria.

Also, I would like to convert automatically the dates of the query, since
the date in the form [Forms]![frmStudentChoice]![BulletinDate] is changed
during each term: January for Winter, May for Summer and September for Fall.
Click to expand...

Sorry, but I don't understand what you mean here.
 
Thanks Marsh
Also, I would like to convert automatically the dates of the query, since
the date in the form [Forms]![frmStudentChoice]![BulletinDate] is changed
during each term: January for Winter, May for Summer and September for
Click to expand...
Fall.

Sorry, but I don't understand what you mean here.
Click to expand...

The control [BulletinDate] in the form [frmStudentChoice] is changed every
new term of studies, for example for the Winter term it is January 1 2004.
This control is used, for example, in the Condition where of a macro like
this: [Inscription-Date]=[Forms]![frmStudentChoice]![DateBulletin]. This is
usefull since I won't have to write "January 1 2004" in the macro.

What I would like to do is how to use the control
[Forms]![frmStudentChoice]![DateBulletin] to substract one year or days to
have the following results: "January 1 2003", "May 1 2003", "September 1
2003"

Thanks again.
 
Telesphore said:
Thanks Marsh
Also, I would like to convert automatically the dates of the query, since
the date in the form [Forms]![frmStudentChoice]![BulletinDate] is changed
during each term: January for Winter, May for Summer and September for
Click to expand...
Fall.

Sorry, but I don't understand what you mean here.
Click to expand...

The control [BulletinDate] in the form [frmStudentChoice] is changed every
new term of studies, for example for the Winter term it is January 1 2004.
This control is used, for example, in the Condition where of a macro like
this: [Inscription-Date]=[Forms]![frmStudentChoice]![DateBulletin]. This is
usefull since I won't have to write "January 1 2004" in the macro.

What I would like to do is how to use the control
[Forms]![frmStudentChoice]![DateBulletin] to substract one year or days to
have the following results: "January 1 2003", "May 1 2003", "September 1
2003"
Click to expand...


You can use the DateAdd function to do this (see Help).

E.g.
DateAdd("q", -4, [Forms]![frmStudentChoice]![DateBulletin])
 
You can use the DateAdd function to do this (see Help).
E.g.
DateAdd("q", -4, [Forms]![frmStudentChoice]![DateBulletin])
Click to expand...

Thanks again,

My problem is that I am using a French version of Access 2000. So it doesn't
work.

BTW, I suppose that the interval "q" is for "quarter". I tried "t" which
should be the equivalent in French for "trimestre", but to no avail.
 
Telesphore said:
You can use the DateAdd function to do this (see Help).

E.g.
DateAdd("q", -4, [Forms]![frmStudentChoice]![DateBulletin])
Click to expand...

Thanks again,

My problem is that I am using a French version of Access 2000. So it doesn't
work.

BTW, I suppose that the interval "q" is for "quarter". I tried "t" which
should be the equivalent in French for "trimestre", but to no avail.
Click to expand...


Help for DateAdd (or whatever it's called in your version)
should provide a list of all the various time units it can
work with.

Yes "q" is for quarter, which, from your question, is what I
thought you wanted.

You didn't say what "doesn't work" means, so I'm not sure
that the problem isn't in the datebuiltin text box???
 
Back
Top