Too Many Fields

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

I need about three hundred fields in a query because of
the large amount of equations I have. If I had to guess,
I am at around 200 right now and I got a message that
said "too many fields defined", is there any way around
this? I know I can combine some of the equations, but I
was wondering it there way a way to extend the number of
fields allowed, thanks , stu
 
Stu said:
I need about three hundred fields in a query because of
the large amount of equations I have. If I had to guess,
I am at around 200 right now and I got a message that
said "too many fields defined", is there any way around
this? I know I can combine some of the equations, but I
was wondering it there way a way to extend the number of
fields allowed, thanks , stu
Click to expand...

Nope.

Consider a design where each formula is a *row* in a child table rather
than a *column*. Then the sky's the limit.
 
I need about three hundred fields in a query because of
the large amount of equations I have. If I had to guess,
I am at around 200 right now and I got a message that
said "too many fields defined", is there any way around
this? I know I can combine some of the equations, but I
was wondering it there way a way to extend the number of
fields allowed, thanks , stu
Click to expand...

No. You're limited to 255 fields in a Query.

What you might want to consider is creating a Form (for onscreen
display) or a Report (for printing), and put a number of these
calculations in the Control Source of textboxes. This will let you see
or print up to some 700 values for each record, while letting you
limit the size of the query to something reasonable (i.e. just the
base fields from which the calculations are done).
 
What if you need some of these calculations for future
queries? I have a similar problem, where I get that the
query is too complex. I only have 229 columns, but I am
writing some formulas that depend on new calculated
columns. Is there any way to conduct the calculations on
the form and somehow get it back into the table or query
so I could query on the results later? The rows option
sounds interesting, but I don't understand how it works.
How about the use of functions?

Ken
 
What if you need some of these calculations for future
queries? I have a similar problem, where I get that the
query is too complex.
Click to expand...

The "Query Too Complex" error arises when the *compiled* query exceeds
64KBytes. Unfortunately the size of the compiled query is buried deep
in the systems tables and is not, to my knowledge, exposed in any
useful way - only manifesting itself with this error!
I only have 229 columns, but I am
writing some formulas that depend on new calculated
columns.
Click to expand...

Well... you usually cannot reuse a calculated expression in a further
calculation; you need to go back to the root.
Is there any way to conduct the calculations on
the form and somehow get it back into the table or query
so I could query on the results later? The rows option
sounds interesting, but I don't understand how it works.
Click to expand...

It's not really an "option", it's just a different way of structuring
your table data.
How about the use of functions?
Click to expand...

That's probably your best bet. Rather than having a massive nest of
IIF statements, calculations, calculations on calculations, etc. you
can just create a VBA module containing functions which accept Variant
arguments (passed from your query as fieldnames); the function would
contain all the logic, looping, etc. needed to do the calculation, and
would return a single value to the query.

This helps a lot with the Query Too Complex error because instead of
having a 60 or 70 byte long expression, you have just

CalcField8: MyFunc([thisfield], [thatfield])
 
Thanks! I have not worked with functions or VBA. I guess
I just need to read up on Functions syntax and create a
module that contains the functions. I guess The functions
would use the fields I reserve in my table? Or would I
create string functions with a Dim statement? Also, in
the QBE Query method, how would I reference my particular
function? Thanks in advance!

Ken
-----Original Message-----
What if you need some of these calculations for future
queries? I have a similar problem, where I get that the
query is too complex.
Click to expand...

The "Query Too Complex" error arises when the *compiled* query exceeds
64KBytes. Unfortunately the size of the compiled query is buried deep
in the systems tables and is not, to my knowledge, exposed in any
useful way - only manifesting itself with this error!
I only have 229 columns, but I am
writing some formulas that depend on new calculated
columns.
Click to expand...

Well... you usually cannot reuse a calculated expression in a further
calculation; you need to go back to the root.
Is there any way to conduct the calculations on
the form and somehow get it back into the table or query
so I could query on the results later? The rows option
sounds interesting, but I don't understand how it
Click to expand...
works.

It's not really an "option", it's just a different way of structuring
your table data.
How about the use of functions?
Click to expand...

That's probably your best bet. Rather than having a massive nest of
IIF statements, calculations, calculations on calculations, etc. you
can just create a VBA module containing functions which accept Variant
arguments (passed from your query as fieldnames); the function would
contain all the logic, looping, etc. needed to do the calculation, and
would return a single value to the query.

This helps a lot with the Query Too Complex error because instead of
having a 60 or 70 byte long expression, you have just

CalcField8: MyFunc([thisfield], [thatfield])



.
Click to expand...
 
Thanks! I have not worked with functions or VBA. I guess
I just need to read up on Functions syntax and create a
module that contains the functions. I guess The functions
would use the fields I reserve in my table? Or would I
create string functions with a Dim statement? Also, in
the QBE Query method, how would I reference my particular
function? Thanks in advance!
Click to expand...

Not having any idea what your functions are intended to do nor what
inputs they require I can only be very general here!

You can put all the functions in one Module (or one in each module if
you prefer). Just remember that the function names and the module
names are from the same "namespace" - don't use the same name for a
function and for a module.

The syntax of a function declaration is

Public Function Functionname(arg1 AS datatype, ...) AS datatype
<zero to thousands of lines of VBA code>
<including somewhere...>
Functionname = <value to be returned>
....
End Function

In your query grid you would just type

SomeName: Functionname([fieldname], [fieldname]...)
 
John

Thanks for your time and patience. I will give it a try on Friday and see if I can test a few formulas. Hopefully, I can get this going. This one problem has help me up and caused me to use stacked queries that have made the structure somewhat convoluted. Thanks again!
 
John,

I set up a test database to play with Functions, using
your suggestions. I got it working! Now, I just have to
adjust my thinking to arrange the formulas to work using
public function-based methodology. Then I can refer to
them in my queries or forms. And, I am not longer limited
to the 255 (or so) columns of the query.

Thanks again!!

Ken
-----Original Message-----
Thanks! I have not worked with functions or VBA. I guess
I just need to read up on Functions syntax and create a
module that contains the functions. I guess The functions
would use the fields I reserve in my table? Or would I
create string functions with a Dim statement? Also, in
the QBE Query method, how would I reference my particular
function? Thanks in advance!
Click to expand...

Not having any idea what your functions are intended to do nor what
inputs they require I can only be very general here!

You can put all the functions in one Module (or one in each module if
you prefer). Just remember that the function names and the module
names are from the same "namespace" - don't use the same name for a
function and for a module.

The syntax of a function declaration is

Public Function Functionname(arg1 AS datatype, ...) AS datatype
<zero to thousands of lines of VBA code>
<including somewhere...>
Functionname = <value to be returned>
....
End Function

In your query grid you would just type

SomeName: Functionname([fieldname], [fieldname]...)




.
Click to expand...
 
One last problem. In my test database, I set up a table
named ProfitTest with two fields: one was 'Cost" and the
other "Units."

Next, I wrote a Public Function called "Price" that
multiplies Cost * Units. I then wrote another Public
Function that calculates off the "Price" Function. It is
called "Score." It uses an IIF statement and provides a
score, depending on the results of the public
Function "Price."

Next, I designed a query that had four
columns: "Cost," "Units," "Price," and "Score." In the
query everything worked. A Price was calculated and the
Score worked. So, I thought all was fine. However, when
I went to save the query, no matter what I try to name the
query, it gives me an error message "Cannot open
database "my newly assigned name." It may not be an
application that your application recognizes, or the file
may be corrupt."

Then, I thought, well I probably haven't saved the Module
that the Public Function is in, therefore, the query
probably can't find the reference. So, I try to save the
module and it asks me to save the following
objects: "Module1" and "Query1." If I say "Yes," it
asks me to save the query and I am thrown back into the
error message I got when trying to save the query.

Could this be that I should have saved the Public Function
first in the module and then created the query and then
saved it? I'm thinking that since I didn't save either
and then tried to save both, that they were tied to each
other and couldn't find each other for the save operation,
although they seemed to find each other for the
calculation phase.

Sorry for the long question.

Ken
-----Original Message-----
Thanks! I have not worked with functions or VBA. I guess
I just need to read up on Functions syntax and create a
module that contains the functions. I guess The functions
would use the fields I reserve in my table? Or would I
create string functions with a Dim statement? Also, in
the QBE Query method, how would I reference my particular
function? Thanks in advance!
Click to expand...

Not having any idea what your functions are intended to do nor what
inputs they require I can only be very general here!

You can put all the functions in one Module (or one in each module if
you prefer). Just remember that the function names and the module
names are from the same "namespace" - don't use the same name for a
function and for a module.

The syntax of a function declaration is

Public Function Functionname(arg1 AS datatype, ...) AS datatype
<zero to thousands of lines of VBA code>
<including somewhere...>
Functionname = <value to be returned>
....
End Function

In your query grid you would just type

SomeName: Functionname([fieldname], [fieldname]...)




.
Click to expand...
 
Never Mind. I figured this out. Apparently, when I set up the database I was
online saving it to a remote server as I am working from home and didn't
want to tie up the phone line.

I logged offline and continued working on the database. When I went to save
it, since I wasn't online, it couldn't locate the database. I just won the
knucklehead award.

I set up my test database and everything worked fine.

Ken

Ken said:
One last problem. In my test database, I set up a table
named ProfitTest with two fields: one was 'Cost" and the
other "Units."

Next, I wrote a Public Function called "Price" that
multiplies Cost * Units. I then wrote another Public
Function that calculates off the "Price" Function. It is
called "Score." It uses an IIF statement and provides a
score, depending on the results of the public
Function "Price."

Next, I designed a query that had four
columns: "Cost," "Units," "Price," and "Score." In the
query everything worked. A Price was calculated and the
Score worked. So, I thought all was fine. However, when
I went to save the query, no matter what I try to name the
query, it gives me an error message "Cannot open
database "my newly assigned name." It may not be an
application that your application recognizes, or the file
may be corrupt."

Then, I thought, well I probably haven't saved the Module
that the Public Function is in, therefore, the query
probably can't find the reference. So, I try to save the
module and it asks me to save the following
objects: "Module1" and "Query1." If I say "Yes," it
asks me to save the query and I am thrown back into the
error message I got when trying to save the query.

Could this be that I should have saved the Public Function
first in the module and then created the query and then
saved it? I'm thinking that since I didn't save either
and then tried to save both, that they were tied to each
other and couldn't find each other for the save operation,
although they seemed to find each other for the
calculation phase.

Sorry for the long question.

Ken
-----Original Message-----
Thanks! I have not worked with functions or VBA. I guess
I just need to read up on Functions syntax and create a
module that contains the functions. I guess The functions
would use the fields I reserve in my table? Or would I
create string functions with a Dim statement? Also, in
the QBE Query method, how would I reference my particular
function? Thanks in advance!
Click to expand...

Not having any idea what your functions are intended to do nor what
inputs they require I can only be very general here!

You can put all the functions in one Module (or one in each module if
you prefer). Just remember that the function names and the module
names are from the same "namespace" - don't use the same name for a
function and for a module.

The syntax of a function declaration is

Public Function Functionname(arg1 AS datatype, ...) AS datatype
<zero to thousands of lines of VBA code>
<including somewhere...>
Functionname = <value to be returned>
....
End Function

In your query grid you would just type

SomeName: Functionname([fieldname], [fieldname]...)




.
Click to expand...
Click to expand...
 
Next, I wrote a Public Function called "Price" that
multiplies Cost * Units. I then wrote another Public
Function that calculates off the "Price" Function. It is
called "Score." It uses an IIF statement and provides a
score, depending on the results of the public
Function "Price."
Click to expand...

Subtle but important rephrasing:

You wrote a public function named Price that multiplies TWO ARGUMENTS
that you pass to it. Those two arguments could be *anything*, the
function doesn't care - it gets two Variants, converts them to
numbers, multiplies them and returns a result.

Perhaps you could post the actual code you used.
Next, I designed a query that had four
columns: "Cost," "Units," "Price," and "Score." In the
query everything worked. A Price was calculated and the
Score worked. So, I thought all was fine. However, when
I went to save the query, no matter what I try to name the
query, it gives me an error message "Cannot open
database "my newly assigned name." It may not be an
application that your application recognizes, or the file
may be corrupt."
Click to expand...

Again, please post the SQL of the query. I suspect you have a syntax
error somewhere and it's trying to reference a Database object rather
than a query or module name.
Then, I thought, well I probably haven't saved the Module
that the Public Function is in, therefore, the query
probably can't find the reference.
Click to expand...

So long as the Function is compiled it shouldn't matter.
So, I try to save the
module and it asks me to save the following
objects: "Module1" and "Query1." If I say "Yes," it
asks me to save the query and I am thrown back into the
error message I got when trying to save the query.
Click to expand...

Sounds like an error in the Query (perhaps quite independent of the
function).
Could this be that I should have saved the Public Function
first in the module and then created the query and then
saved it? I'm thinking that since I didn't save either
and then tried to save both, that they were tied to each
other and couldn't find each other for the save operation,
although they seemed to find each other for the
calculation phase.
Click to expand...

Post the code and the query and we'll see - I think there's some error
in the query; saved or not saved isn't the issue.
 
Back
Top