too many fields.

[kaosyeti@comcast.net via AccessMonster.com]

hey.. i have a report that is based on a 12 field simple query. i've been
working on it for a while and had to restart some areas so i know i went over
the 755 control limit but only until i reset the report with the
saveas/rename trick. however, that's not helping me now. i only have about
150 controls in the report right now but when i try to run it, it won't even
open the report. i keep getting an error about having too many fields. i DO
reference a lot of controls in the report inside other controls in the report
and many of them have nested iif statements, but i don't know if that's the
problem. any thoughts? (ms access 2003, windows xp)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200601/1

[kaosyeti@comcast.net via AccessMonster.com]

something else strange... i tried to separate the report out a little into
subforms, but just one page had the same result. my formulas are LARGE but i
didn't think that would be a problem. is there a simpler way to do this that
would be more acceptable:

=IIf([txtboxquarter]=1,(Sum(Abs([datesold] Between DateSerial(Forms!
formquarterly!txtboxyear,1,1) And DateSerial(Forms!formquarterly!txtboxyear,3,
31) And [useddept]="Retail" And [Source]="New Trade")*[saleprice])-Sum(Abs(
[datesold] Between DateSerial(Forms!formquarterly!txtboxyear,1,1) And
DateSerial(Forms!formquarterly!txtboxyear,3,31) And [useddept]="Retail" And
[Source]="New Trade")*[cost]))/[txtboxunitsNTR],IIf([txtboxquarter]=2,(Sum
(Abs([datesold] Between DateSerial(Forms!formquarterly!txtboxyear,4,1) And
DateSerial(Forms!formquarterly!txtboxyear,6,30) And [useddept]="Retail" And
[Source]="New Trade")*[saleprice])-Sum(Abs([datesold] Between DateSerial
(Forms!formquarterly!txtboxyear,4,1) And DateSerial(Forms!formquarterly!
txtboxyear,6,30) And [useddept]="Retail" And [Source]="New Trade")*[cost]))/
[txtboxunitsNTR],IIf([txtboxquarter]=3,(Sum(Abs([datesold] Between DateSerial
(Forms!formquarterly!txtboxyear,7,1) And DateSerial(Forms!formquarterly!
txtboxyear,9,30) And [useddept]="Retail" And [Source]="New Trade")*[saleprice]
)-Sum(Abs([datesold] Between DateSerial(Forms!formquarterly!txtboxyear,7,1)
And DateSerial(Forms!formquarterly!txtboxyear,9,30) And [useddept]="Retail"
And [Source]="New Trade")*[cost]))/[txtboxunitsNTR],(Sum(Abs([datesold]
Between DateSerial(Forms!formquarterly!txtboxyear,10,1) And DateSerial(Forms!
formquarterly!txtboxyear,12,31) And [useddept]="Retail" And [Source]="New
Trade")*[saleprice])-Sum(Abs([datesold] Between DateSerial(Forms!
formquarterly!txtboxyear,10,1) And DateSerial(Forms!formquarterly!txtboxyear,
12,31) And [useddept]="Retail" And [Source]="New Trade")*[cost]))/
[txtboxunitsNTR])))

there are 36 text boxes on this page, each with a formula similar to this one.
is this the problem?

[Larry Linson]

Put that code into a function and call the function as the Control Source,
as a start. What you have is difficult to follow and difficult to maintain.
Whether it will result in a "Too Many Fields" message, I couldn't say.

Larry Linson
Microsoft Access MVP

"kaos...@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:5b0061d1ea7ea@uwe...

[kaosyeti@comcast.net via AccessMonster.com]

ok... next question is how do i call a function as the control source of a
textbox? i've still new-ish to vba.

Larry Linson wrote:
>Put that code into a function and call the function as the Control Source,
>as a start. What you have is difficult to follow and difficult to maintain.
>Whether it will result in a "Too Many Fields" message, I couldn't say.
>
> Larry Linson
> Microsoft Access MVP
>

>> something else strange... i tried to separate the report out a little into
>> subforms, but just one page had the same result. my formulas are LARGE

>[quoted text clipped - 57 lines]

[Larry Linson]

"kaos...@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message

> ok... next question is how do i call a

> function as the control source of a
> textbox? i've still new-ish to vba.

Put the function in the Form's module. If the function name is YourFunc,
then open the Property Sheet for the TextBox and set its Control Source to
=YourFunc(). Must be a Function, a Sub procedure will not work in this
context.

[kaosyeti@comcast.net via AccessMonster.com]

good to know. that will come in handy in a LOT of other places for me, i'm
sure. thanks.

--