Add additional data to a report based on specific criteria

  • Thread starter Thread starter JSprawls
  • Start date Start date
J

JSprawls

I have a field in a Access 2007 Report called 'FREQ' who's data is either
"EW" (stands for every week) or "EOW" (every other week). What I need is if
the FREQ field equals "EOW" I want a grid (4 rows 6 columns) to appear in my
report that contains a predefined date in the first box (lets say Jan/01)
then in the next box a date that is 7 days after the first (Jan/08) and so on
until all 24 boxes are filled in. IF the "FREQ" field is "EW" do not create
the grid just continue reading data in from the query.
 
Use a subreport to show the grid under the desired conditions:

1. Create a table with just one field of type Number, named (say) CountID.
Mark it as the primary key field. Save the table as tblCount. Enter the
numbers 0 to 23.

2. Create a report (to be used as a subreport showing the grid.)
Use tblCount as its Record Source.
Add a text box with properties:
Control Source = #1/1/2009# + 7 * [CountID]
Format General Date
Width 1" (6 x 1" cells + 2 x 1" margins fits 8"
page)
Height 0.2" (or however tall you want you cells in the
grid)
Border Color 0 (assuming you want to show grid lines.)

3. Add another text box with these properties:
Control Source = "EOW"
Name txtShow
Visible No
Place it on top of the othe text box, so it uses no additional space.

4. Open the Page Setup dialog.
Set margins of 1".
On the 3rd tab (Columns), specify:
Columns 3
Row Spacing 0
Column Spacing 0
Same As Detail checked
Column Layout Across, then Down

5. Save. Test. You should see the 6 x 4 grid, with dates. Close.

6. On your main report, add this as a subreport.
Set these properties for the subreport control:
Link Master Fields FREQ
Link Child Fields txtShow
Width 6.02"
Can Grow Yes
 
Back
Top