Too many fields

  • Thread starter Thread starter Jeanne
  • Start date Start date
J

Jeanne

Thanks for your help. Here is my SQL statement. SELECT [EPV PO].[PO
State], [EPV PO].[Account ID], [EPV PO].[Account Description], [EPV PO].[Job
ID], [EPV PO].Vendor, [EPV PO].[Good Thru], [EPV PO].Requestor, [EPV
PO].Date, [EPV PO].[PO No], [EPV PO].[Line Description], [EPV PO].[Vendor
ID], [EPV PO].QtyOrdered, [EPV PO].QtyReceived, [EPV PO].QtyRemaining, [EPV
PO].[Item ID], [EPV PO].[Unit Price], [EPV PO].Amount, [EPV PO].[Ship To
City], [EPV PO].[Ship To State]
FROM [EPV PO]
WHERE ((([EPV PO].Date) Between [Forms]![FilterForm]![POFrom] And
[Forms]![FilterForm]![POTo]) AND (([EPV PO].[Requestor Select])=Yes) AND
(([EPV PO].[Job Select])=Yes) AND (([EPV PO].[GL Select])=Yes) AND (([EPV
PO].[GoodThru Select])=Yes) AND (([EPV PO].[Status Select])=Yes) AND (([EPV
PO].[Vendor Select])=Yes) AND (([EPV PO].[PO Select])=Yes));


There are 26 or 27 fields in the query
 
Jeanne

Typically, you'd respond to the folks who responded to your original post,
in the same thread, rather than starting a new one. This helps by keeping
the Qs and the As together in one place.

I don't recall what specification Access has for the maximum number of
fields a query can display, but you could find that under "specifications"
in Access HELP.

Have you tried making a backup copy of the query, then "pruning back", one
field at a time, until it DOES work?

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Sorry If I overstepped. I don't do this often.

My query has only 26-27 fields, nowhere near the 255 allowed. There are no
sophisticated relationships. It's just one table with multiple selection
criteria. That's why I'm confused about the error message. There are no
dynamic fields, either. Every single field is in the table.

Any suggestions greatly appreciated.

JJ
Jeff Boyce said:
Jeanne

Typically, you'd respond to the folks who responded to your original post,
in the same thread, rather than starting a new one. This helps by keeping
the Qs and the As together in one place.

I don't recall what specification Access has for the maximum number of
fields a query can display, but you could find that under "specifications"
in Access HELP.

Have you tried making a backup copy of the query, then "pruning back", one
field at a time, until it DOES work?

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeanne said:
Thanks for your help. Here is my SQL statement. SELECT [EPV PO].[PO
State], [EPV PO].[Account ID], [EPV PO].[Account Description], [EPV
PO].[Job ID], [EPV PO].Vendor, [EPV PO].[Good Thru], [EPV PO].Requestor,
[EPV PO].Date, [EPV PO].[PO No], [EPV PO].[Line Description], [EPV
PO].[Vendor ID], [EPV PO].QtyOrdered, [EPV PO].QtyReceived, [EPV
PO].QtyRemaining, [EPV PO].[Item ID], [EPV PO].[Unit Price], [EPV
PO].Amount, [EPV PO].[Ship To City], [EPV PO].[Ship To State]
FROM [EPV PO]
WHERE ((([EPV PO].Date) Between [Forms]![FilterForm]![POFrom] And
[Forms]![FilterForm]![POTo]) AND (([EPV PO].[Requestor Select])=Yes) AND
(([EPV PO].[Job Select])=Yes) AND (([EPV PO].[GL Select])=Yes) AND (([EPV
PO].[GoodThru Select])=Yes) AND (([EPV PO].[Status Select])=Yes) AND
(([EPV PO].[Vendor Select])=Yes) AND (([EPV PO].[PO Select])=Yes));


There are 26 or 27 fields in the query
Click to expand...
Click to expand...
 
Jeanne

Typically, you'd respond to the folks who responded to your original post,
in the same thread, rather than starting a new one.  This helps by keeping
the Qs and the As together in one place.

I don't recall what specification Access has for the maximum number of
fields a query can display, but you could find that under "specifications"
in Access HELP.

Have you tried making a backup copy of the query, then "pruning back", one
field at a time, until it DOES work?

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP




Thanks for your help.  Here is my SQL statement.  SELECT [EPV PO].[PO
State], [EPV PO].[Account ID], [EPV PO].[Account Description], [EPV
PO].[Job ID], [EPV PO].Vendor, [EPV PO].[Good Thru], [EPV PO].Requestor,
[EPV PO].Date, [EPV PO].[PO No], [EPV PO].[Line Description], [EPV
PO].[Vendor ID], [EPV PO].QtyOrdered, [EPV PO].QtyReceived, [EPV
PO].QtyRemaining, [EPV PO].[Item ID], [EPV PO].[Unit Price], [EPV
PO].Amount, [EPV PO].[Ship To City], [EPV PO].[Ship To State]
FROM [EPV PO]
WHERE ((([EPV PO].Date) Between [Forms]![FilterForm]![POFrom] And
[Forms]![FilterForm]![POTo]) AND (([EPV PO].[Requestor Select])=Yes) AND
(([EPV PO].[Job Select])=Yes) AND (([EPV PO].[GL Select])=Yes) AND (([EPV
PO].[GoodThru Select])=Yes) AND (([EPV PO].[Status Select])=Yes) AND
(([EPV PO].[Vendor Select])=Yes) AND (([EPV PO].[PO Select])=Yes));
Click to expand...
There are 26 or 27 fields in the query- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I had a similar situation that revolved around the WHERE statement
having too many criteria.

is that the situation.

if so, maybe, you could create a query that limits your list to some
of those [field] = yes criteria and then use that query as the source
of your parameter query.
 
Did you try the "pruning back" suggestion?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeanne said:
Sorry If I overstepped. I don't do this often.

My query has only 26-27 fields, nowhere near the 255 allowed. There are
no sophisticated relationships. It's just one table with multiple
selection criteria. That's why I'm confused about the error message.
There are no dynamic fields, either. Every single field is in the table.

Any suggestions greatly appreciated.

JJ
Jeff Boyce said:
Jeanne

Typically, you'd respond to the folks who responded to your original
post, in the same thread, rather than starting a new one. This helps by
keeping the Qs and the As together in one place.

I don't recall what specification Access has for the maximum number of
fields a query can display, but you could find that under
"specifications" in Access HELP.

Have you tried making a backup copy of the query, then "pruning back",
one field at a time, until it DOES work?

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeanne said:
Thanks for your help. Here is my SQL statement. SELECT [EPV PO].[PO
State], [EPV PO].[Account ID], [EPV PO].[Account Description], [EPV
PO].[Job ID], [EPV PO].Vendor, [EPV PO].[Good Thru], [EPV PO].Requestor,
[EPV PO].Date, [EPV PO].[PO No], [EPV PO].[Line Description], [EPV
PO].[Vendor ID], [EPV PO].QtyOrdered, [EPV PO].QtyReceived, [EPV
PO].QtyRemaining, [EPV PO].[Item ID], [EPV PO].[Unit Price], [EPV
PO].Amount, [EPV PO].[Ship To City], [EPV PO].[Ship To State]
FROM [EPV PO]
WHERE ((([EPV PO].Date) Between [Forms]![FilterForm]![POFrom] And
[Forms]![FilterForm]![POTo]) AND (([EPV PO].[Requestor Select])=Yes) AND
(([EPV PO].[Job Select])=Yes) AND (([EPV PO].[GL Select])=Yes) AND
(([EPV PO].[GoodThru Select])=Yes) AND (([EPV PO].[Status Select])=Yes)
AND (([EPV PO].[Vendor Select])=Yes) AND (([EPV PO].[PO Select])=Yes));


There are 26 or 27 fields in the query
Click to expand...
Click to expand...
Click to expand...
 
Sorry If I overstepped.  I don't do this often.

My query has only 26-27 fields, nowhere near the 255 allowed.  There areno
sophisticated relationships.  It's just one table with multiple selection
criteria.  That's why I'm confused about the error message.  There areno
dynamic fields, either.  Every single field is in the table.

Any suggestions greatly appreciated.




Jeanne
Click to expand...
Typically, you'd respond to the folks who responded to your original post,
in the same thread, rather than starting a new one.  This helps by keeping
the Qs and the As together in one place.
Click to expand...
I don't recall what specification Access has for the maximum number of
fields a query can display, but you could find that under "specifications"
in Access HELP.
Click to expand...
Have you tried making a backup copy of the query, then "pruning back", one
field at a time, until it DOES work?
Click to expand...
JOPO (just one person's opinion)

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Jeanne said:
Thanks for your help.  Here is my SQL statement.  SELECT [EPV PO].[PO
State], [EPV PO].[Account ID], [EPV PO].[Account Description], [EPV
PO].[Job ID], [EPV PO].Vendor, [EPV PO].[Good Thru], [EPV PO].Requestor,
[EPV PO].Date, [EPV PO].[PO No], [EPV PO].[Line Description], [EPV
PO].[Vendor ID], [EPV PO].QtyOrdered, [EPV PO].QtyReceived, [EPV
PO].QtyRemaining, [EPV PO].[Item ID], [EPV PO].[Unit Price], [EPV
PO].Amount, [EPV PO].[Ship To City], [EPV PO].[Ship To State]
FROM [EPV PO]
WHERE ((([EPV PO].Date) Between [Forms]![FilterForm]![POFrom] And
[Forms]![FilterForm]![POTo]) AND (([EPV PO].[Requestor Select])=Yes) AND
(([EPV PO].[Job Select])=Yes) AND (([EPV PO].[GL Select])=Yes) AND (([EPV
PO].[GoodThru Select])=Yes) AND (([EPV PO].[Status Select])=Yes) AND
(([EPV PO].[Vendor Select])=Yes) AND (([EPV PO].[PO Select])=Yes));
There are 26 or 27 fields in the query- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

What is the specific error statement?

I encountered a very similar situation with a SIMILAR where statement.
 
Sorry If I overstepped.  I don't do this often.
Click to expand...
My query has only 26-27 fields, nowhere near the 255 allowed.  There are no
sophisticated relationships.  It's just one table with multiple selection
criteria.  That's why I'm confused about the error message.  There are no
dynamic fields, either.  Every single field is in the table.
Click to expand...
Any suggestions greatly appreciated.
Click to expand...
news:ubm%[email protected]...
Click to expand...
Jeanne
Typically, you'd respond to the folks who responded to your original post,
in the same thread, rather than starting a new one.  This helps by keeping
the Qs and the As together in one place.
I don't recall what specification Access has for the maximum number of
fields a query can display, but you could find that under "specifications"
in Access HELP.
Have you tried making a backup copy of the query, then "pruning back",one
field at a time, until it DOES work?
JOPO (just one person's opinion)
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks for your help.  Here is my SQL statement.  SELECT [EPV PO]..[PO
State], [EPV PO].[Account ID], [EPV PO].[Account Description], [EPV
PO].[Job ID], [EPV PO].Vendor, [EPV PO].[Good Thru], [EPV PO].Requestor,
[EPV PO].Date, [EPV PO].[PO No], [EPV PO].[Line Description], [EPV
PO].[Vendor ID], [EPV PO].QtyOrdered, [EPV PO].QtyReceived, [EPV
PO].QtyRemaining, [EPV PO].[Item ID], [EPV PO].[Unit Price], [EPV
PO].Amount, [EPV PO].[Ship To City], [EPV PO].[Ship To State]
FROM [EPV PO]
WHERE ((([EPV PO].Date) Between [Forms]![FilterForm]![POFrom] And
[Forms]![FilterForm]![POTo]) AND (([EPV PO].[Requestor Select])=Yes) AND
(([EPV PO].[Job Select])=Yes) AND (([EPV PO].[GL Select])=Yes) AND (([EPV
PO].[GoodThru Select])=Yes) AND (([EPV PO].[Status Select])=Yes) AND
(([EPV PO].[Vendor Select])=Yes) AND (([EPV PO].[PO Select])=Yes));
There are 26 or 27 fields in the query- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

What is the specific error statement?

I encountered a very similar situation with a SIMILAR where statement.- Hide quoted text -

- Show quoted text -
Click to expand...

I built a table with similar fields and copied and pasted you query in
sql mode and it runs
 
Have you tried replacing "Yes" with True or -1? If these are in fact Yes/No
datatypes, I'd use -1

You might also want to define your parameter:Forms!FilterForm!POFrom

If you have never done this, it significantly helps resolve parameter issues.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Jeanne said:
Sorry If I overstepped. I don't do this often.

My query has only 26-27 fields, nowhere near the 255 allowed. There are no
sophisticated relationships. It's just one table with multiple selection
criteria. That's why I'm confused about the error message. There are no
dynamic fields, either. Every single field is in the table.

Any suggestions greatly appreciated.

JJ
Jeff Boyce said:
Jeanne

Typically, you'd respond to the folks who responded to your original post,
in the same thread, rather than starting a new one. This helps by keeping
the Qs and the As together in one place.

I don't recall what specification Access has for the maximum number of
fields a query can display, but you could find that under "specifications"
in Access HELP.

Have you tried making a backup copy of the query, then "pruning back", one
field at a time, until it DOES work?

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeanne said:
Thanks for your help. Here is my SQL statement. SELECT [EPV PO].[PO
State], [EPV PO].[Account ID], [EPV PO].[Account Description], [EPV
PO].[Job ID], [EPV PO].Vendor, [EPV PO].[Good Thru], [EPV PO].Requestor,
[EPV PO].Date, [EPV PO].[PO No], [EPV PO].[Line Description], [EPV
PO].[Vendor ID], [EPV PO].QtyOrdered, [EPV PO].QtyReceived, [EPV
PO].QtyRemaining, [EPV PO].[Item ID], [EPV PO].[Unit Price], [EPV
PO].Amount, [EPV PO].[Ship To City], [EPV PO].[Ship To State]
FROM [EPV PO]
WHERE ((([EPV PO].Date) Between [Forms]![FilterForm]![POFrom] And
[Forms]![FilterForm]![POTo]) AND (([EPV PO].[Requestor Select])=Yes) AND
(([EPV PO].[Job Select])=Yes) AND (([EPV PO].[GL Select])=Yes) AND (([EPV
PO].[GoodThru Select])=Yes) AND (([EPV PO].[Status Select])=Yes) AND
(([EPV PO].[Vendor Select])=Yes) AND (([EPV PO].[PO Select])=Yes));


There are 26 or 27 fields in the query
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help. Here is my SQL statement. SELECT [EPV PO].[PO
State], [EPV PO].[Account ID], [EPV PO].[Account Description], [EPV PO].[Job
ID], [EPV PO].Vendor, [EPV PO].[Good Thru], [EPV PO].Requestor, [EPV
PO].Date, [EPV PO].[PO No], [EPV PO].[Line Description], [EPV PO].[Vendor
ID], [EPV PO].QtyOrdered, [EPV PO].QtyReceived, [EPV PO].QtyRemaining, [EPV
PO].[Item ID], [EPV PO].[Unit Price], [EPV PO].Amount, [EPV PO].[Ship To
City], [EPV PO].[Ship To State]
FROM [EPV PO]
WHERE ((([EPV PO].Date) Between [Forms]![FilterForm]![POFrom] And
[Forms]![FilterForm]![POTo]) AND (([EPV PO].[Requestor Select])=Yes) AND
(([EPV PO].[Job Select])=Yes) AND (([EPV PO].[GL Select])=Yes) AND (([EPV
PO].[GoodThru Select])=Yes) AND (([EPV PO].[Status Select])=Yes) AND (([EPV
PO].[Vendor Select])=Yes) AND (([EPV PO].[PO Select])=Yes));


There are 26 or 27 fields in the query
Click to expand...

Hrm. That is a very modest query and should certainly not - by itself - pop
this error. Have you tried Compacting and Repairing the database? You might
also want to copy this SQL out to notepad; delete the query; compact and
repair; create a new query in SQL view and paste this back in. I'm guessing
there is some corruption somewhere in the database.
 
Back
Top