Formula in report field too long

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

I have a system that keeps track of annual contracts – the contracts can
cross over 2 years. I created a year field and 6 month fields (bi-monthly)
for each year. I run a report for the sales team each every other month to
let them know what clients they have and if any are up for renewal. The
client is marked with an asterisk if they are up for renewal. I have the
code below in a field to the left of the detail data. This report and many
other reports are very easy to create and run with this design (I know I
could have built the code more efficient. Ok, here is the problem, the
contracts are now monthly. I have adjusted the tables and reports and made
12 month fields for both years. When I expanded the code to check for
renewals to include the other six months it is too long to go in the field.
Any suggestions?

Thanks for your help!

=IIf([Year1]=Right([OpenArgs],4) And
IIf(Left([OpenArgs],3)="Jan",[jan1]=True,IIf(Left([OpenArgs],3)="Mar",[mar1]=True,IIf(Left([OpenArgs],3)="May",[may1]=True,IIf(Left([OpenArgs],3)="Jul",[jul1]=True,IIf(Left([OpenArgs],3)="Sep",[sep1]=True,IIf(Left([OpenArgs],3)="Nov",[Nov1]=True)))))),"",IIf([Year2]=Right([OpenArgs],4)
And
IIf(Left([OpenArgs],3)="Jan",[jan2]=True,IIf(Left([OpenArgs],3)="Mar",[mar2]=True,IIf(Left([OpenArgs],3)="May",[may2]=True,IIf(Left([OpenArgs],3)="Jul",[jul2]=True,IIf(Left([OpenArgs],3)="Sep",[sep2]=True,IIf(Left([OpenArgs],3)="Nov",[Nov2]=True)))))),"","*"))

Again, thanks - Sarah
 
What happens when the contract is up? Do you try to get the client to sign
up for another contract again?

If so, it seems likely that one client will have multiple contracts over the
years, so you have a one-to-many relation between clients anc contracts.
That would suggest using 2 tables like this:
Client table, with fields:
- ClientID AutoNumber
- ClientName Text
Contract table, with fields:
- ContractID AutoNumber
- ClientID Number who this contract is for
- StartDate Date/Time when this contract starts
- EndDate Date/Time when this contract ends.

To determine the contracts that are current at a particular date, create a
query using both tables. Type a date into the Field row, e.g.:
#10/1/2008#
In the Criteria row beneath this, enter:
Between [StartDate] And [EndDate]

This data structure allows you to record contracts that go for 2 years, or 1
month, or anything else. And it's dead easy to query current ones at any
date. If you want the query to read the 'as of' date from a form, replace
the #10/1/2008# in the Field row with a reference to the text box on your
form, e.g.:
[Forms].[Form1].[Text0]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sarah said:
I have a system that keeps track of annual contracts – the contracts can
cross over 2 years. I created a year field and 6 month fields
(bi-monthly)
for each year. I run a report for the sales team each every other month
to
let them know what clients they have and if any are up for renewal. The
client is marked with an asterisk if they are up for renewal. I have the
code below in a field to the left of the detail data. This report and
many
other reports are very easy to create and run with this design (I know I
could have built the code more efficient. Ok, here is the problem, the
contracts are now monthly. I have adjusted the tables and reports and
made
12 month fields for both years. When I expanded the code to check for
renewals to include the other six months it is too long to go in the
field.
Any suggestions?

Thanks for your help!

=IIf([Year1]=Right([OpenArgs],4) And
IIf(Left([OpenArgs],3)="Jan",[jan1]=True,IIf(Left([OpenArgs],3)="Mar",[mar1]=True,IIf(Left([OpenArgs],3)="May",[may1]=True,IIf(Left([OpenArgs],3)="Jul",[jul1]=True,IIf(Left([OpenArgs],3)="Sep",[sep1]=True,IIf(Left([OpenArgs],3)="Nov",[Nov1]=True)))))),"",IIf([Year2]=Right([OpenArgs],4)
And
IIf(Left([OpenArgs],3)="Jan",[jan2]=True,IIf(Left([OpenArgs],3)="Mar",[mar2]=True,IIf(Left([OpenArgs],3)="May",[may2]=True,IIf(Left([OpenArgs],3)="Jul",[jul2]=True,IIf(Left([OpenArgs],3)="Sep",[sep2]=True,IIf(Left([OpenArgs],3)="Nov",[Nov2]=True)))))),"","*"))

Again, thanks - Sarah
Click to expand...
 
Hi Allen,

Thank you for the information below. I really appreciate your time and
expertise.

I do have the one to many relationship with the client and the contract
tables. I did not setup the system with a begin and end date in the contract
because it is a system for a magazine and we need to indicate exactly which
issues the client will be running in. That is why I used a year field and
then 12 Y/N check boxes to represent each issue they will run in. I did this
for 2 years since a contract can crossover. A begin and end date would not
be able to show when a client needs to skip an issue. What is the character
count on a text field in a report? Is there another way I can make this work>

Thank you again for your help. I appreciate all the times you have helped
me out of my Access problems.

Best Regards, Sarah

Allen Browne said:
What happens when the contract is up? Do you try to get the client to sign
up for another contract again?

If so, it seems likely that one client will have multiple contracts over the
years, so you have a one-to-many relation between clients anc contracts.
That would suggest using 2 tables like this:
Client table, with fields:
- ClientID AutoNumber
- ClientName Text
Contract table, with fields:
- ContractID AutoNumber
- ClientID Number who this contract is for
- StartDate Date/Time when this contract starts
- EndDate Date/Time when this contract ends.

To determine the contracts that are current at a particular date, create a
query using both tables. Type a date into the Field row, e.g.:
#10/1/2008#
In the Criteria row beneath this, enter:
Between [StartDate] And [EndDate]

This data structure allows you to record contracts that go for 2 years, or 1
month, or anything else. And it's dead easy to query current ones at any
date. If you want the query to read the 'as of' date from a form, replace
the #10/1/2008# in the Field row with a reference to the text box on your
form, e.g.:
[Forms].[Form1].[Text0]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sarah said:
I have a system that keeps track of annual contracts – the contracts can
cross over 2 years. I created a year field and 6 month fields
(bi-monthly)
for each year. I run a report for the sales team each every other month
to
let them know what clients they have and if any are up for renewal. The
client is marked with an asterisk if they are up for renewal. I have the
code below in a field to the left of the detail data. This report and
many
other reports are very easy to create and run with this design (I know I
could have built the code more efficient. Ok, here is the problem, the
contracts are now monthly. I have adjusted the tables and reports and
made
12 month fields for both years. When I expanded the code to check for
renewals to include the other six months it is too long to go in the
field.
Any suggestions?

Thanks for your help!

=IIf([Year1]=Right([OpenArgs],4) And
IIf(Left([OpenArgs],3)="Jan",[jan1]=True,IIf(Left([OpenArgs],3)="Mar",[mar1]=True,IIf(Left([OpenArgs],3)="May",[may1]=True,IIf(Left([OpenArgs],3)="Jul",[jul1]=True,IIf(Left([OpenArgs],3)="Sep",[sep1]=True,IIf(Left([OpenArgs],3)="Nov",[Nov1]=True)))))),"",IIf([Year2]=Right([OpenArgs],4)
And
IIf(Left([OpenArgs],3)="Jan",[jan2]=True,IIf(Left([OpenArgs],3)="Mar",[mar2]=True,IIf(Left([OpenArgs],3)="May",[may2]=True,IIf(Left([OpenArgs],3)="Jul",[jul2]=True,IIf(Left([OpenArgs],3)="Sep",[sep2]=True,IIf(Left([OpenArgs],3)="Nov",[Nov2]=True)))))),"","*"))

Again, thanks - Sarah
Click to expand...
Click to expand...
 
Okay, so you need to record specific months per client:

It sounds like a contract contains several specific ads for the specific
issues of a publication it should run in. This suggests that you need a
ContractDetail table to record the specific line items for the contract. It
would have fields like this:

ContractDetailID AutoNumber primary key
ContractID which contract this row belongs to
PublicationID which publication this refers to
IssueDate which issue of this publication

You probably already have a table of publications, and you probably need a
related table that defines each issue of each publication.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sarah said:
Hi Allen,

Thank you for the information below. I really appreciate your time and
expertise.

I do have the one to many relationship with the client and the contract
tables. I did not setup the system with a begin and end date in the
contract
because it is a system for a magazine and we need to indicate exactly
which
issues the client will be running in. That is why I used a year field and
then 12 Y/N check boxes to represent each issue they will run in. I did
this
for 2 years since a contract can crossover. A begin and end date would
not
be able to show when a client needs to skip an issue. What is the
character
count on a text field in a report? Is there another way I can make this
work>

Thank you again for your help. I appreciate all the times you have helped
me out of my Access problems.

Best Regards, Sarah

Allen Browne said:
What happens when the contract is up? Do you try to get the client to
sign
up for another contract again?

If so, it seems likely that one client will have multiple contracts over
the
years, so you have a one-to-many relation between clients anc contracts.
That would suggest using 2 tables like this:
Client table, with fields:
- ClientID AutoNumber
- ClientName Text
Contract table, with fields:
- ContractID AutoNumber
- ClientID Number who this contract is for
- StartDate Date/Time when this contract starts
- EndDate Date/Time when this contract ends.

To determine the contracts that are current at a particular date, create
a
query using both tables. Type a date into the Field row, e.g.:
#10/1/2008#
In the Criteria row beneath this, enter:
Between [StartDate] And [EndDate]

This data structure allows you to record contracts that go for 2 years,
or 1
month, or anything else. And it's dead easy to query current ones at any
date. If you want the query to read the 'as of' date from a form, replace
the #10/1/2008# in the Field row with a reference to the text box on your
form, e.g.:
[Forms].[Form1].[Text0]

Sarah said:
I have a system that keeps track of annual contracts – the contracts can
cross over 2 years. I created a year field and 6 month fields
(bi-monthly)
for each year. I run a report for the sales team each every other
month
to
let them know what clients they have and if any are up for renewal.
The
client is marked with an asterisk if they are up for renewal. I have
the
code below in a field to the left of the detail data. This report and
many
other reports are very easy to create and run with this design (I know
I
could have built the code more efficient. Ok, here is the problem, the
contracts are now monthly. I have adjusted the tables and reports and
made
12 month fields for both years. When I expanded the code to check for
renewals to include the other six months it is too long to go in the
field.
Any suggestions?

Thanks for your help!

=IIf([Year1]=Right([OpenArgs],4) And
IIf(Left([OpenArgs],3)="Jan",[jan1]=True,IIf(Left([OpenArgs],3)="Mar",[mar1]=True,IIf(Left([OpenArgs],3)="May",[may1]=True,IIf(Left([OpenArgs],3)="Jul",[jul1]=True,IIf(Left([OpenArgs],3)="Sep",[sep1]=True,IIf(Left([OpenArgs],3)="Nov",[Nov1]=True)))))),"",IIf([Year2]=Right([OpenArgs],4)
And
IIf(Left([OpenArgs],3)="Jan",[jan2]=True,IIf(Left([OpenArgs],3)="Mar",[mar2]=True,IIf(Left([OpenArgs],3)="May",[may2]=True,IIf(Left([OpenArgs],3)="Jul",[jul2]=True,IIf(Left([OpenArgs],3)="Sep",[sep2]=True,IIf(Left([OpenArgs],3)="Nov",[Nov2]=True)))))),"","*"))
Click to expand...
Click to expand...
Click to expand...
 
=IIf([Year1]=Right([OpenArgs],4) And
IIf(Left([OpenArgs],3)="Jan",[jan1]=True,
IIf(Left([OpenArgs],3)="Mar",[mar1]=True,
IIf(Left([OpenArgs],3)="May",[may1]=True,
IIf(Left([OpenArgs],3)="Jul",[jul1]=True,
IIf(Left([OpenArgs],3)="Sep",[sep1]=True,
IIf(Left([OpenArgs],3)="Nov",[Nov1]=True)))))),"",
IIf([Year2]=Right([OpenArgs],4)
And
IIf(Left([OpenArgs],3)="Jan",
[jan2]=True,IIf(Left([OpenArgs],3)="Mar",
[mar2]=True,IIf(Left([OpenArgs],3)="May",
[may2]=True,IIf(Left([OpenArgs],3)="Jul",
[jul2]=True,IIf(Left([OpenArgs],3)="Sep",
[sep2]=True,IIf(Left([OpenArgs],3)="Nov",
[Nov2]=True)))))),"","*"))

Instead of trying to do that in the control, you might want to try
creating a VBA function to do this. That MIGHT look something like the
following.

Private Function fSetValue()
Dim strYear as String
Dim strMonth as String

strYear = Right(Me.OpenArgs,4)
strMonth= Left(Me.OpenArgs,3)

If strYear = Me.Year1 then
Me.Controls(StrMonth & "1")= _
Me.Controls(strMonth).Name = strMonth & "1"

elseif strYear = Me.Year2 then
Me.Controls(StrMonth & "2")= _
Me.Controls(strMonth).Name = strMonth & "2"

End if

End Function

Then for each control you needed to use this you would enter
=fSetValue()

I'm not sure this will work since I am guessing how your table structure
is set up and how the report structure is set up.

A better table design (see Allen Browne's suggestions) would certainly
make this easier and would preclude having to use VBA to accomplish the
task.

Another option would be to write a VBA function that would use the
format event of the detail section and loop through all the controls for
the various months.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a system that keeps track of annual contracts – the contracts can
cross over 2 years. I created a year field and 6 month fields (bi-monthly)
for each year. I run a report for the sales team each every other month to
let them know what clients they have and if any are up for renewal. The
client is marked with an asterisk if they are up for renewal. I have the
code below in a field to the left of the detail data. This report and many
other reports are very easy to create and run with this design (I know I
could have built the code more efficient. Ok, here is the problem, the
contracts are now monthly. I have adjusted the tables and reports and made
12 month fields for both years. When I expanded the code to check for
renewals to include the other six months it is too long to go in the field.
Any suggestions?

Thanks for your help!

=IIf([Year1]=Right([OpenArgs],4) And
IIf(Left([OpenArgs],3)="Jan",[jan1]=True,IIf(Left([OpenArgs],3)="Mar",[mar1]=True,IIf(Left([OpenArgs],3)="May",[may1]=True,IIf(Left([OpenArgs],3)="Jul",[jul1]=True,IIf(Left([OpenArgs],3)="Sep",[sep1]=True,IIf(Left([OpenArgs],3)="Nov",[Nov1]=True)))))),"",IIf([Year2]=Right([OpenArgs],4)
And
IIf(Left([OpenArgs],3)="Jan",[jan2]=True,IIf(Left([OpenArgs],3)="Mar",[mar2]=True,IIf(Left([OpenArgs],3)="May",[may2]=True,IIf(Left([OpenArgs],3)="Jul",[jul2]=True,IIf(Left([OpenArgs],3)="Sep",[sep2]=True,IIf(Left([OpenArgs],3)="Nov",[Nov2]=True)))))),"","*"))

Again, thanks - Sarah
Click to expand...
 
Back
Top