Build Expression using multiple Queries and "Between/And"

[SA wannabe nerdgirl]

I am building a report where I need to know the number of times a date fell
within a fiscal year.

Example: Between 8/1/2007 and 7/31/2008, there were 512 students. (the 512
is coming from a column of dates in a report, where 512 dates were entered
that fell between the above said time period.)

I have gotten as far a query for this to work, so I would like to put all
these queries into one report, listing the fiscal years on the left and the
student total on the right. But the wizard only let's me setup with one
query, not multiples.

Secondly, when I put the =Count for the column - it repeats itself! I tried
to move this combo box up to the header, but then the number disappears and
all that shows is the label!

Thank you for all your help in advance!

 

[Philip Herlihy]

I am building a report where I need to know the number of times a date fell
within a fiscal year.

Example: Between 8/1/2007 and 7/31/2008, there were 512 students. (the 512
is coming from a column of dates in a report, where 512 dates were entered
that fell between the above said time period.)

I have gotten as far a query for this to work, so I would like to put all
these queries into one report, listing the fiscal years on the left and the
student total on the right. But the wizard only let's me setup with one
query, not multiples.

Secondly, when I put the =Count for the column - it repeats itself! I tried
to move this combo box up to the header, but then the number disappears and
all that shows is the label!

Thank you for all your help in advance!

One of us doesn't quite understand what you've written...

Assuming your table design is right, you've declared any relationships
in the Relationships window, and you've created a query which brings
together the information you need in your report, the Report Wizard will
do a pretty good job of laying out the fields for you. It sounds,
though, as if you need to use the "Sorting and Grouping" button on the
toolbar. It looks like a space invader on its side, or a battleship gun
turret (depending on your point of view).

You can add fields to group on, and also decide on what interval to
group - for a date this can be months, years, etc.

Your problem is that your fiscal year isn't the calendar year, so you
need a way of identifying the fiscal year from a particular date. My
guess is that the best way to do this (and in truth the only way I can
think of!) would be to create a custom function in a VBA module, so that
you could include a function call in the query that underlies your
report. The call would look something like this:
FiscalYear: = fiscal([RegistrationDate])

You should be able to devise a combination of the date functions
available in Access VBA which will do this. I'd write it for you, but
I'm in imminent danger of wearing my dinner...


HTH

Phil, London

 

[SA wannabe nerdgirl]

Oh dear, now I'm thoroughly confused. Let's rethink this.

In a report, in a combo box that does not repeat down the page, I want to
write an expression that does the following

=Count([Appt_Table]![Date_Submitted] = (Between #8/1/2007# And #7/31/2008#))

I know it is not syntactically correct, but the idea is to look at a column
in a table and count how many records fit into this specified period of time
and show this number in a report.

I am building a report where I need to know the number of times a date fell
within a fiscal year.

Example: Between 8/1/2007 and 7/31/2008, there were 512 students. (the 512
is coming from a column of dates in a report, where 512 dates were entered
that fell between the above said time period.)

I have gotten as far a query for this to work, so I would like to put all
these queries into one report, listing the fiscal years on the left and the
student total on the right. But the wizard only let's me setup with one
query, not multiples.

Secondly, when I put the =Count for the column - it repeats itself! I tried
to move this combo box up to the header, but then the number disappears and
all that shows is the label!

Thank you for all your help in advance!

One of us doesn't quite understand what you've written...

Assuming your table design is right, you've declared any relationships
in the Relationships window, and you've created a query which brings
together the information you need in your report, the Report Wizard will
do a pretty good job of laying out the fields for you. It sounds,
though, as if you need to use the "Sorting and Grouping" button on the
toolbar. It looks like a space invader on its side, or a battleship gun
turret (depending on your point of view).

You can add fields to group on, and also decide on what interval to
group - for a date this can be months, years, etc.

Your problem is that your fiscal year isn't the calendar year, so you
need a way of identifying the fiscal year from a particular date. My
guess is that the best way to do this (and in truth the only way I can
think of!) would be to create a custom function in a VBA module, so that
you could include a function call in the query that underlies your
report. The call would look something like this:
FiscalYear: = fiscal([RegistrationDate])

You should be able to devise a combination of the date functions
available in Access VBA which will do this. I'd write it for you, but
I'm in imminent danger of wearing my dinner...


HTH

Phil, London

 

[John W. Vinson]

Oh dear, now I'm thoroughly confused. Let's rethink this.

In a report, in a combo box that does not repeat down the page, I want to
write an expression that does the following

=Count([Appt_Table]![Date_Submitted] = (Between #8/1/2007# And #7/31/2008#))

I know it is not syntactically correct, but the idea is to look at a column
in a table and count how many records fit into this specified period of time
and show this number in a report.

The = operator searches for a single value exact match.

The Between operator searches for a value in a range.

Use one, or the other - not both!!!

That said, combo boxes are not really all that appropriate for reports. You
could set the Control Source property of a Textbox on the report to


=DCount("*", "[Appt_Table]", "[Date_Submitted] Between #8/1/2007# And
#7/31/2008#")

or if you want a given fiscal year's count,

=DCount("*", "[Appt_Table]", "[Date_Submitted] Between DateSerial([Enter FY:]
- 1, 8, 1) And DateSerial([Enter FY:], 7, 31)")

 

[SA wannabe nerdgirl]

*dancing in her cubicle*

Thank you! the first solution worked! Thank you!!!!

 

[John W. Vinson]

*dancing in her cubicle*

Thank you! the first solution worked! Thank you!!!!

<high-five and big grin>

You're welcome. Great dancing btw.

 

[SA wannabe nerdgirl]

Since I'm on a roll now with the fiscal year, is there someway that I can use
=DCount for this report:

an expression that counts the number of completed travels during a period of
time for a particular country

example: 214 completed travels during fiscal year 07-08 to Australia

 

[John W. Vinson]

Since I'm on a roll now with the fiscal year, is there someway that I can use
=DCount for this report:

an expression that counts the number of completed travels during a period of
time for a particular country

example: 214 completed travels during fiscal year 07-08 to Australia

I'm sure you can, but since I know nothing about the structure of your tables,
how "travels" are recorded, how they're identified as completed, or how the
destination (or stop in a multistop trip??) might be recorded, I'm unable to
say how.

A Totals query will probably be more efficient than a DCount though.

 

[SA wannabe nerdgirl]

Table information:

Country Withdraw Date_Submitted
Australia No 6/31/08
Australia No 7/15/08
Austria Yes 3/8/08
Chile No 10/1/09

So the above output would look something like below:

Country 8/1/07-7/31/08
Australia 2
Austria 0
Chile 0

Reasoning why Austria is 0 is because they withdrawed from the program.
Chile is 0 because the Date for it is not within the date range (fiscal year).

That's the idea behind the confusion. I'm going to look into the Total query.

Thanks again for all your help,

Definite Newbie: SA wannabe nerdgirl