Unique report header

[James O]

Ok, hopefully this is easy hehe,

I have a table with many fields... and a query using that table one of the
fields is a number this number represents how many days until the record's
contract is fulfilled. This field in the query is [Fulfilled]-date() , the
number show up properly. Ok I am trying to make a report using this query,
and I want there to be 4 headers.

Contract Ending in les than 3 months
Ending between 3 months to 1 year
1year to 5 years
More than 5 years.

So I am thinking that I need to make a table with a duration field and a
Description field and add it to the query.

so :

Duration Description
<90 Less than 3 months
between 90 and 365 etc...

But im not sure how to bring it all together, thanks for your insight.

 

[Marshall Barton]

I have a table with many fields... and a query using that table one of the
fields is a number this number represents how many days until the record's
contract is fulfilled. This field in the query is [Fulfilled]-date() , the
number show up properly. Ok I am trying to make a report using this query,
and I want there to be 4 headers.

Contract Ending in les than 3 months
Ending between 3 months to 1 year
1year to 5 years
More than 5 years.

So I am thinking that I need to make a table with a duration field and a
Description field and add it to the query.

so :

Duration Description
<90 Less than 3 months
between 90 and 365 etc...

The Durations table will be easier to deal with if it has 3
fields:

DaysLow DaysHigh Descr
0 90 Less than 3 months
90 365 3 months to 1 year
. . .

Then you can join that table to your data in the report's
record source query something along these lines:

SELECT table.*, Durations.Descr
FROM table INNER JOIN Durations
ON (Fulfilled - date() >= DaysLow)
And (Fulfilled - date() < DaysHigh)

You can not specify this kind of Join in the query design
grid so you will have to work in SQL view.

 

[JoyAA]

Given the field in the query [Fulfilled]-date() is labeled Expr1, add the
following to your report.

Under the header labeled Contract Ending in less than 3 months, add a text
box with the following controlsource:
=IIf([Expr1]<=90,[Expr1],Null)

Under the header labeled Contract Ending between 3 months to 1 year, add a
text box with the following controlsource:
=IIf([Expr1]>90 And [expr1]<=365,[Expr1],Null)

Under the header labeled Contract Ending between 1 year to 5 years, add a
text box with the following controlsource:
=IIf([Expr1]>365 And [expr1]<=1825,[Expr1],Null)

Under the header labeled Contract Ending in more than 5 years, add a text
box with the following controlsource:
=IIf([expr1]>1825,[Expr1],Null)

Please change the numbers to make the math right if its not, but hopefully
this helps you...