Too many fields

[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

 

[Jeff Boyce]

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]

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

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

 

[vbasean]

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 -

- Show quoted text -

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.

 

[Jeff Boyce]

Did you try the "pruning back" suggestion?

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

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

 

[vbasean]

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

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)

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 -

- Show quoted text -

What is the specific error statement?

I encountered a very similar situation with a SIMILAR where statement.

 

[vbasean]

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.

news:ubm%[email protected]...

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 -

- Show quoted text -

What is the specific error statement?

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

- Show quoted text -

I built a table with similar fields and copied and pasted you query in
sql mode and it runs

 

[Dale Fye]

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.

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

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

 

[John W. Vinson]

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

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.