P
pwdpwd
Hi
I'd be really grateful for any pointers for a problem that is driving me nuts!
I am trying to change a 'static' report into a 'dynamic' one.
I have a scheduling application, originally written in Access 97 which
generates service dates for trucks - usually around 8-10 'Service Dates' per
year. One of the features of the app is that 1 report shows on one page (A4
landscape) all the trucks, grouped by Owner, that have a Service Date in each
week - 53 columns wide!
I managed that by creating a select query and then into a CrossTab Query,
where the Owner & Vehicle Reg No are the row headings and a Count of the
Value of the Service Date gives me the 53 columns (there can only ever be 1
Service Date in any 1 week) with a '1' in each of the 53 columns where a
service date is scheduled. Because of the limitations of space with 53
columns, I used an If statement in the Control Source of fields for each of
the 53 columns on the report, replacing an 'X' in a Non-Blank field from the
query.
I couldnt make the report dynamic, as Access 97 didnt allow Vertical text, so
I made the select query 'Static' by using the year as criteria for a
formatted expression of the Service Date so as to include the correct dates.
Those queries and that report works fine.
I then needed to add an indication of when an MOT was to take place (always
done on a service date); I did this by having a select query that shows the
dates when an MOT is due (indicated by the user via an option button against
each service date), then to a CrossTab (using the same principles as
described above) and a Subreport which works fine on its own. I include this
subreport invisibly on the main report, again for each year. I use the Contol
source of the fields in the main report to check whether the equivalent field
in the subreport is Non Blank, in which case I show a 'T' - else an 'X' or
nothing. This report also works fine.
I now am bringing the app up to date and I want to make it dynamic, by having
the user input a year, and then have the report produce the relevant data
automatically. I now have a 'generic' report fed by select and CrossTab
queries where the select query criteria requires an input from the user in
the format for the Year; on its own, this report works fine..
The problem is that when I include the subreport, fed by equivalent generic
queries requiring a similar input, the report will not work. On running the
report I have to input the year from the 'main' query, but instead of being
required to input the year for the subreport once, I am prompted to enter the
year dozens (or even hundreds) of times. When I replace the criteria in the
subreport select query with a number for a year (and hence make it 'non-
generic' then the report works fine.
I know that I could create another 10 years worth of queries and reports, but
know that there must be a better way.
Thanks for any help.
I'd be really grateful for any pointers for a problem that is driving me nuts!
I am trying to change a 'static' report into a 'dynamic' one.
I have a scheduling application, originally written in Access 97 which
generates service dates for trucks - usually around 8-10 'Service Dates' per
year. One of the features of the app is that 1 report shows on one page (A4
landscape) all the trucks, grouped by Owner, that have a Service Date in each
week - 53 columns wide!
I managed that by creating a select query and then into a CrossTab Query,
where the Owner & Vehicle Reg No are the row headings and a Count of the
Value of the Service Date gives me the 53 columns (there can only ever be 1
Service Date in any 1 week) with a '1' in each of the 53 columns where a
service date is scheduled. Because of the limitations of space with 53
columns, I used an If statement in the Control Source of fields for each of
the 53 columns on the report, replacing an 'X' in a Non-Blank field from the
query.
I couldnt make the report dynamic, as Access 97 didnt allow Vertical text, so
I made the select query 'Static' by using the year as criteria for a
formatted expression of the Service Date so as to include the correct dates.
Those queries and that report works fine.
I then needed to add an indication of when an MOT was to take place (always
done on a service date); I did this by having a select query that shows the
dates when an MOT is due (indicated by the user via an option button against
each service date), then to a CrossTab (using the same principles as
described above) and a Subreport which works fine on its own. I include this
subreport invisibly on the main report, again for each year. I use the Contol
source of the fields in the main report to check whether the equivalent field
in the subreport is Non Blank, in which case I show a 'T' - else an 'X' or
nothing. This report also works fine.
I now am bringing the app up to date and I want to make it dynamic, by having
the user input a year, and then have the report produce the relevant data
automatically. I now have a 'generic' report fed by select and CrossTab
queries where the select query criteria requires an input from the user in
the format for the Year; on its own, this report works fine..
The problem is that when I include the subreport, fed by equivalent generic
queries requiring a similar input, the report will not work. On running the
report I have to input the year from the 'main' query, but instead of being
required to input the year for the subreport once, I am prompted to enter the
year dozens (or even hundreds) of times. When I replace the criteria in the
subreport select query with a number for a year (and hence make it 'non-
generic' then the report works fine.
I know that I could create another 10 years worth of queries and reports, but
know that there must be a better way.
Thanks for any help.