query to complex

R

Ron

I have developed a SQL statement to coordinate a subform with 8 text boxes
in a search form with the following code:

SELECT WarehouseQuery.ID, WarehouseQuery.BoatNumber,
WarehouseQuery.SJCNumber, WarehouseQuery.QTY, WarehouseQuery.DESCRIPTION,
WarehouseQuery.Wh_Date_received, WarehouseQuery.Wh_QtyReceived,
WarehouseQuery.Wh_Location, WarehouseQuery.KitInfo,
WarehouseQuery.Wh_Comments, WarehouseQuery.Wh_Assignment,
WarehouseQuery.Date_received, WarehouseQuery.MFGNumber,
WarehouseQuery.[SupplierNumber], WarehouseQuery.Supplier_ID,
WarehouseQuery.Use_On_Boat, WarehouseQuery.Date_Ordered,
WarehouseQuery.PONumber, WarehouseQuery.Price_Per_Unit,
WarehouseQuery.COMMENTS, WarehouseQuery.Thickness_Width,
WarehouseQuery.Length, WarehouseQuery.TaskID, WarehouseQuery.JobCode,
WarehouseQuery.Inventory, WarehouseQuery.PHASE

FROM WarehouseQuery

WHERE (((WarehouseQuery.BoatNumber)=[Forms]![QBF_Form]![txtWhatBoat] Or
[Forms]![QBF_Form]![txtWhatBoat] Is Null) AND
((WarehouseQuery.SJCNumber)=[Forms]![QBF_Form]![txtWhatSJCNumber] Or
[Forms]![QBF_Form]![txtWhatSJCNumber] Is Null) AND
((WarehouseQuery.DESCRIPTION)=[Forms]![QBF_Form]![txtWhatDescription] Or
[Forms]![QBF_Form]![txtWhatDescription] Is Null) AND
((WarehouseQuery.MFGNumber)=[Forms]![QBF_Form]![txtMFGNumber] Or
[Forms]![QBF_Form]![txtMFGNumber] Is Null) AND
((WarehouseQuery.[SupplierNumber])=[Forms]![QBF_Form]![txtWhatSupplierNumber
] Or [Forms]![QBF_Form]![txtWhatSupplierNumber] Is Null) AND
((WarehouseQuery.Supplier_ID)=[Forms]![QBF_Form]![txtWhatSupplier] Or
[Forms]![QBF_Form]![txtWhatSupplier] Is Null) AND
((WarehouseQuery.PONumber)=[Forms]![QBF_Form]![txtWhatPONumber] Or
[Forms]![QBF_Form]![txtWhatPONumber] Is Null) AND
((WarehouseQuery.JobCode)=[Forms]![QBF_Form]![txtWhatJobCode] Or
[Forms]![QBF_Form]![txtWhatJobCode] Is Null));



The text boxes can be left null or can have a value to determine what shows
up in the sub form. Sometimes it works fine and other times I get a message
that the query is too long and will be trunkated or the query is too
complex. Does anyone have any ideas what can be done to get consistant
results.



Thanks for any help,

Ron
 
V

Van T. Dinh

It may / may not help the consistency but will make it easier to read by
using Alias and remove some of the parentheses (Access is trigger-happy with
adding parentheses) like:

========
SELECT WQ.ID, WQ.BoatNumber, WQ.SJCNumber, WQ.QTY, WQ.DESCRIPTION,
WQ.Wh_Date_received, WQ.Wh_QtyReceived, WQ.Wh_Location,
WQ.KitInfo, WQ.Wh_Comments, WQ.Wh_Assignment, WQ.Date_received,
WQ.MFGNumber, WQ.[SupplierNumber], WQ.Supplier_ID,
WQ.Use_On_Boat, WQ.Date_Ordered, WQ.PONumber, WQ.Price_Per_Unit,
WQ.COMMENTS, WQ.Thickness_Width, WQ.Length, WQ.TaskID,
WQ.JobCode, WQ.Inventory, WQ.PHASE

FROM WarehouseQuery AS WQ

WHERE ( ( WQ.BoatNumber=[Forms]![QBF_Form]![txtWhatBoat] )
Or ( [Forms]![QBF_Form]![txtWhatBoat] Is Null ) )
AND ( ( WQ.SJCNumber=[Forms]![QBF_Form]![txtWhatSJCNumber] )
Or ( [Forms]![QBF_Form]![txtWhatSJCNumber] Is Null ) )
AND ( (WQ.DESCRIPTION=[Forms]![QBF_Form]![txtWhatDescription] )
Or ( [Forms]![QBF_Form]![txtWhatDescription] Is Null ) )
AND ( ( WQ.MFGNumber=[Forms]![QBF_Form]![txtMFGNumber] )
Or ( [Forms]![QBF_Form]![txtMFGNumber] Is Null ) )
AND ( (
WQ.[SupplierNumber]=[Forms]![QBF_Form]![txtWhatSupplierNumber] )
Or ( [Forms]![QBF_Form]![txtWhatSupplierNumber] Is Null ) )
AND ( (WQ.Supplier_ID=[Forms]![QBF_Form]![txtWhatSupplier] )
Or ( [Forms]![QBF_Form]![txtWhatSupplier] Is Null ) )
AND ( ( WQ.PONumber=[Forms]![QBF_Form]![txtWhatPONumber] )
Or ([Forms]![QBF_Form]![txtWhatPONumber] Is Null ) )
AND ( ( WQ.JobCode=[Forms]![QBF_Form]![txtWhatJobCode])
Or ( [Forms]![QBF_Form]![txtWhatJobCode] Is Null ) );
========

--
HTH
Van T. Dinh
MVP (Access)



Ron said:
I have developed a SQL statement to coordinate a subform with 8 text boxes
in a search form with the following code:

SELECT WarehouseQuery.ID, WarehouseQuery.BoatNumber,
WarehouseQuery.SJCNumber, WarehouseQuery.QTY, WarehouseQuery.DESCRIPTION,
WarehouseQuery.Wh_Date_received, WarehouseQuery.Wh_QtyReceived,
WarehouseQuery.Wh_Location, WarehouseQuery.KitInfo,
WarehouseQuery.Wh_Comments, WarehouseQuery.Wh_Assignment,
WarehouseQuery.Date_received, WarehouseQuery.MFGNumber,
WarehouseQuery.[SupplierNumber], WarehouseQuery.Supplier_ID,
WarehouseQuery.Use_On_Boat, WarehouseQuery.Date_Ordered,
WarehouseQuery.PONumber, WarehouseQuery.Price_Per_Unit,
WarehouseQuery.COMMENTS, WarehouseQuery.Thickness_Width,
WarehouseQuery.Length, WarehouseQuery.TaskID, WarehouseQuery.JobCode,
WarehouseQuery.Inventory, WarehouseQuery.PHASE

FROM WarehouseQuery

WHERE (((WarehouseQuery.BoatNumber)=[Forms]![QBF_Form]![txtWhatBoat] Or
[Forms]![QBF_Form]![txtWhatBoat] Is Null) AND
((WarehouseQuery.SJCNumber)=[Forms]![QBF_Form]![txtWhatSJCNumber] Or
[Forms]![QBF_Form]![txtWhatSJCNumber] Is Null) AND
((WarehouseQuery.DESCRIPTION)=[Forms]![QBF_Form]![txtWhatDescription] Or
[Forms]![QBF_Form]![txtWhatDescription] Is Null) AND
((WarehouseQuery.MFGNumber)=[Forms]![QBF_Form]![txtMFGNumber] Or
[Forms]![QBF_Form]![txtMFGNumber] Is Null) AND
((WarehouseQuery.[SupplierNumber])=[Forms]![QBF_Form]![txtWhatSupplierNumber
] Or [Forms]![QBF_Form]![txtWhatSupplierNumber] Is Null) AND
((WarehouseQuery.Supplier_ID)=[Forms]![QBF_Form]![txtWhatSupplier] Or
[Forms]![QBF_Form]![txtWhatSupplier] Is Null) AND
((WarehouseQuery.PONumber)=[Forms]![QBF_Form]![txtWhatPONumber] Or
[Forms]![QBF_Form]![txtWhatPONumber] Is Null) AND
((WarehouseQuery.JobCode)=[Forms]![QBF_Form]![txtWhatJobCode] Or
[Forms]![QBF_Form]![txtWhatJobCode] Is Null));



The text boxes can be left null or can have a value to determine what
shows
up in the sub form. Sometimes it works fine and other times I get a
message
that the query is too long and will be trunkated or the query is too
complex. Does anyone have any ideas what can be done to get consistant
results.



Thanks for any help,

Ron
Click to expand...
 
R

Ron

Thanks Van
I tried it and so far it is working but I'll have to wait and see if it
continues to work. It was inconsistant before also.
Van T. Dinh said:
It may / may not help the consistency but will make it easier to read by
using Alias and remove some of the parentheses (Access is trigger-happy with
adding parentheses) like:

========
SELECT WQ.ID, WQ.BoatNumber, WQ.SJCNumber, WQ.QTY, WQ.DESCRIPTION,
WQ.Wh_Date_received, WQ.Wh_QtyReceived, WQ.Wh_Location,
WQ.KitInfo, WQ.Wh_Comments, WQ.Wh_Assignment, WQ.Date_received,
WQ.MFGNumber, WQ.[SupplierNumber], WQ.Supplier_ID,
WQ.Use_On_Boat, WQ.Date_Ordered, WQ.PONumber, WQ.Price_Per_Unit,
WQ.COMMENTS, WQ.Thickness_Width, WQ.Length, WQ.TaskID,
WQ.JobCode, WQ.Inventory, WQ.PHASE

FROM WarehouseQuery AS WQ

WHERE ( ( WQ.BoatNumber=[Forms]![QBF_Form]![txtWhatBoat] )
Or ( [Forms]![QBF_Form]![txtWhatBoat] Is Null ) )
AND ( ( WQ.SJCNumber=[Forms]![QBF_Form]![txtWhatSJCNumber] )
Or ( [Forms]![QBF_Form]![txtWhatSJCNumber] Is Null ) )
AND ( (WQ.DESCRIPTION=[Forms]![QBF_Form]![txtWhatDescription] )
Or ( [Forms]![QBF_Form]![txtWhatDescription] Is Null ) )
AND ( ( WQ.MFGNumber=[Forms]![QBF_Form]![txtMFGNumber] )
Or ( [Forms]![QBF_Form]![txtMFGNumber] Is Null ) )
AND ( (
WQ.[SupplierNumber]=[Forms]![QBF_Form]![txtWhatSupplierNumber] )
Or ( [Forms]![QBF_Form]![txtWhatSupplierNumber] Is Null ) )
AND ( (WQ.Supplier_ID=[Forms]![QBF_Form]![txtWhatSupplier] )
Or ( [Forms]![QBF_Form]![txtWhatSupplier] Is Null ) )
AND ( ( WQ.PONumber=[Forms]![QBF_Form]![txtWhatPONumber] )
Or ([Forms]![QBF_Form]![txtWhatPONumber] Is Null ) )
AND ( ( WQ.JobCode=[Forms]![QBF_Form]![txtWhatJobCode])
Or ( [Forms]![QBF_Form]![txtWhatJobCode] Is Null ) );
========

--
HTH
Van T. Dinh
MVP (Access)



Ron said:
I have developed a SQL statement to coordinate a subform with 8 text boxes
in a search form with the following code:

SELECT WarehouseQuery.ID, WarehouseQuery.BoatNumber,
WarehouseQuery.SJCNumber, WarehouseQuery.QTY, WarehouseQuery.DESCRIPTION,
WarehouseQuery.Wh_Date_received, WarehouseQuery.Wh_QtyReceived,
WarehouseQuery.Wh_Location, WarehouseQuery.KitInfo,
WarehouseQuery.Wh_Comments, WarehouseQuery.Wh_Assignment,
WarehouseQuery.Date_received, WarehouseQuery.MFGNumber,
WarehouseQuery.[SupplierNumber], WarehouseQuery.Supplier_ID,
WarehouseQuery.Use_On_Boat, WarehouseQuery.Date_Ordered,
WarehouseQuery.PONumber, WarehouseQuery.Price_Per_Unit,
WarehouseQuery.COMMENTS, WarehouseQuery.Thickness_Width,
WarehouseQuery.Length, WarehouseQuery.TaskID, WarehouseQuery.JobCode,
WarehouseQuery.Inventory, WarehouseQuery.PHASE

FROM WarehouseQuery

WHERE (((WarehouseQuery.BoatNumber)=[Forms]![QBF_Form]![txtWhatBoat] Or
[Forms]![QBF_Form]![txtWhatBoat] Is Null) AND
((WarehouseQuery.SJCNumber)=[Forms]![QBF_Form]![txtWhatSJCNumber] Or
[Forms]![QBF_Form]![txtWhatSJCNumber] Is Null) AND
((WarehouseQuery.DESCRIPTION)=[Forms]![QBF_Form]![txtWhatDescription] Or
[Forms]![QBF_Form]![txtWhatDescription] Is Null) AND
((WarehouseQuery.MFGNumber)=[Forms]![QBF_Form]![txtMFGNumber] Or
[Forms]![QBF_Form]![txtMFGNumber] Is Null) AND
((WarehouseQuery.[SupplierNumber])=[Forms]![QBF_Form]![txtWhatSupplierNumber
] Or [Forms]![QBF_Form]![txtWhatSupplierNumber] Is Null) AND
((WarehouseQuery.Supplier_ID)=[Forms]![QBF_Form]![txtWhatSupplier] Or
[Forms]![QBF_Form]![txtWhatSupplier] Is Null) AND
((WarehouseQuery.PONumber)=[Forms]![QBF_Form]![txtWhatPONumber] Or
[Forms]![QBF_Form]![txtWhatPONumber] Is Null) AND
((WarehouseQuery.JobCode)=[Forms]![QBF_Form]![txtWhatJobCode] Or
[Forms]![QBF_Form]![txtWhatJobCode] Is Null));



The text boxes can be left null or can have a value to determine what
shows
up in the sub form. Sometimes it works fine and other times I get a
message
that the query is too long and will be trunkated or the query is too
complex. Does anyone have any ideas what can be done to get consistant
results.



Thanks for any help,

Ron
Click to expand...
Click to expand...
 
R

Ron

Well it was working a few times but now I get measages that say it is too
long to edit or it is too complex when I try to look at the code.
Van T. Dinh said:
It may / may not help the consistency but will make it easier to read by
using Alias and remove some of the parentheses (Access is trigger-happy with
adding parentheses) like:

========
SELECT WQ.ID, WQ.BoatNumber, WQ.SJCNumber, WQ.QTY, WQ.DESCRIPTION,
WQ.Wh_Date_received, WQ.Wh_QtyReceived, WQ.Wh_Location,
WQ.KitInfo, WQ.Wh_Comments, WQ.Wh_Assignment, WQ.Date_received,
WQ.MFGNumber, WQ.[SupplierNumber], WQ.Supplier_ID,
WQ.Use_On_Boat, WQ.Date_Ordered, WQ.PONumber, WQ.Price_Per_Unit,
WQ.COMMENTS, WQ.Thickness_Width, WQ.Length, WQ.TaskID,
WQ.JobCode, WQ.Inventory, WQ.PHASE

FROM WarehouseQuery AS WQ

WHERE ( ( WQ.BoatNumber=[Forms]![QBF_Form]![txtWhatBoat] )
Or ( [Forms]![QBF_Form]![txtWhatBoat] Is Null ) )
AND ( ( WQ.SJCNumber=[Forms]![QBF_Form]![txtWhatSJCNumber] )
Or ( [Forms]![QBF_Form]![txtWhatSJCNumber] Is Null ) )
AND ( (WQ.DESCRIPTION=[Forms]![QBF_Form]![txtWhatDescription] )
Or ( [Forms]![QBF_Form]![txtWhatDescription] Is Null ) )
AND ( ( WQ.MFGNumber=[Forms]![QBF_Form]![txtMFGNumber] )
Or ( [Forms]![QBF_Form]![txtMFGNumber] Is Null ) )
AND ( (
WQ.[SupplierNumber]=[Forms]![QBF_Form]![txtWhatSupplierNumber] )
Or ( [Forms]![QBF_Form]![txtWhatSupplierNumber] Is Null ) )
AND ( (WQ.Supplier_ID=[Forms]![QBF_Form]![txtWhatSupplier] )
Or ( [Forms]![QBF_Form]![txtWhatSupplier] Is Null ) )
AND ( ( WQ.PONumber=[Forms]![QBF_Form]![txtWhatPONumber] )
Or ([Forms]![QBF_Form]![txtWhatPONumber] Is Null ) )
AND ( ( WQ.JobCode=[Forms]![QBF_Form]![txtWhatJobCode])
Or ( [Forms]![QBF_Form]![txtWhatJobCode] Is Null ) );
========

--
HTH
Van T. Dinh
MVP (Access)



Ron said:
I have developed a SQL statement to coordinate a subform with 8 text boxes
in a search form with the following code:

SELECT WarehouseQuery.ID, WarehouseQuery.BoatNumber,
WarehouseQuery.SJCNumber, WarehouseQuery.QTY, WarehouseQuery.DESCRIPTION,
WarehouseQuery.Wh_Date_received, WarehouseQuery.Wh_QtyReceived,
WarehouseQuery.Wh_Location, WarehouseQuery.KitInfo,
WarehouseQuery.Wh_Comments, WarehouseQuery.Wh_Assignment,
WarehouseQuery.Date_received, WarehouseQuery.MFGNumber,
WarehouseQuery.[SupplierNumber], WarehouseQuery.Supplier_ID,
WarehouseQuery.Use_On_Boat, WarehouseQuery.Date_Ordered,
WarehouseQuery.PONumber, WarehouseQuery.Price_Per_Unit,
WarehouseQuery.COMMENTS, WarehouseQuery.Thickness_Width,
WarehouseQuery.Length, WarehouseQuery.TaskID, WarehouseQuery.JobCode,
WarehouseQuery.Inventory, WarehouseQuery.PHASE

FROM WarehouseQuery

WHERE (((WarehouseQuery.BoatNumber)=[Forms]![QBF_Form]![txtWhatBoat] Or
[Forms]![QBF_Form]![txtWhatBoat] Is Null) AND
((WarehouseQuery.SJCNumber)=[Forms]![QBF_Form]![txtWhatSJCNumber] Or
[Forms]![QBF_Form]![txtWhatSJCNumber] Is Null) AND
((WarehouseQuery.DESCRIPTION)=[Forms]![QBF_Form]![txtWhatDescription] Or
[Forms]![QBF_Form]![txtWhatDescription] Is Null) AND
((WarehouseQuery.MFGNumber)=[Forms]![QBF_Form]![txtMFGNumber] Or
[Forms]![QBF_Form]![txtMFGNumber] Is Null) AND
((WarehouseQuery.[SupplierNumber])=[Forms]![QBF_Form]![txtWhatSupplierNumber
] Or [Forms]![QBF_Form]![txtWhatSupplierNumber] Is Null) AND
((WarehouseQuery.Supplier_ID)=[Forms]![QBF_Form]![txtWhatSupplier] Or
[Forms]![QBF_Form]![txtWhatSupplier] Is Null) AND
((WarehouseQuery.PONumber)=[Forms]![QBF_Form]![txtWhatPONumber] Or
[Forms]![QBF_Form]![txtWhatPONumber] Is Null) AND
((WarehouseQuery.JobCode)=[Forms]![QBF_Form]![txtWhatJobCode] Or
[Forms]![QBF_Form]![txtWhatJobCode] Is Null));



The text boxes can be left null or can have a value to determine what
shows
up in the sub form. Sometimes it works fine and other times I get a
message
that the query is too long and will be trunkated or the query is too
complex. Does anyone have any ideas what can be done to get consistant
results.



Thanks for any help,

Ron
Click to expand...
Click to expand...
 
V

Van T. Dinh

You're welcome ... Glad to help ...

If the Query still does not work consistently, the probable cause is the
automatic type-casting on the value(s) in the unbound TextBoxes on the Form
to the same data-types of the Fields may be incorrect. You may need to use
the (explicit) type-conversion functions to direct Access / JET to the
correct types.
 
V

Van T. Dinh

You didn't switch to the "Design View", did you?

As soon as you switch to "Design View", Access will re-interpret /
re-arrange the SQL.

Change the Query back to what I posted and stick to "SQL View" and
"Datasheet View" and never switched to "Design View".

See my other reply also.
 
R

Ron

I took both suggestions and came up with the following code:

WQ.Wh_Date_received, WQ.Wh_QtyReceived, WQ.Wh_Location,
WQ.KitInfo, WQ.Wh_Comments, WQ.Wh_Assignment, WQ.Date_received,
WQ.MFGNumber, WQ.[SupplierNumber], WQ.Supplier_ID,
WQ.Use_On_Boat, WQ.Date_Ordered, WQ.PONumber, WQ.Price_Per_Unit,
WQ.COMMENTS, WQ.Thickness_Width, WQ.Length, WQ.TaskID,
WQ.JobCode, WQ.Inventory, WQ.PHASE

FROM WarehouseQuery AS WQ

WHERE ( ( WQ.BoatNumber=[Forms]![Q]![txBoat] )
Or ( [Forms]![Q]![txBoat] Is Null ) )
AND ( ( WQ.SJCNumber=[Forms]![Q]![txSJCN] )
Or ( [Forms]![Q]![tx SJCN] Is Null ) )
AND ( (WQ.DESCRIPTION=[Forms]![Q]![txDesc] )
Or ( [Forms]![Q]![txDesc] Is Null ) )
AND ( ( WQ.MFGNumber=[Forms]![Q]![txMFG] )
Or ( [Forms]![Q]![txMFG] Is Null ) )
AND ( (
WQ.[SupplierNumber]=[Forms]![Q]![txSupN] )
Or ( [Forms]![Q]![txSupN] Is Null ) )
AND ( (WQ.Supplier_ID=[Forms]![Q]![txSup] )
Or ( [Forms]![Q]![txSup] Is Null ) )
AND ( ( WQ.PONumber=[Forms]![Q]![txPO] )
Or ([Forms]![Q]![txPO] Is Null ) )
AND ( ( WQ.JobCode=[Forms]![Q]![txJC])
Or ( [Forms]![Q]![txJC] Is Null ) );

The forms are working but I still get messages about complexity being too
great but I can live with that as long as it only affects trying to edit
things. I am really surprised that the complexity was a function of the
number of characters in the code. It never occured to me. Is there an upper
limit to the number of characters in a SQL statement?


Ron said:
I have developed a SQL statement to coordinate a subform with 8 text boxes
in a search form with the following code:

SELECT WarehouseQuery.ID, WarehouseQuery.BoatNumber,
WarehouseQuery.SJCNumber, WarehouseQuery.QTY, WarehouseQuery.DESCRIPTION,
WarehouseQuery.Wh_Date_received, WarehouseQuery.Wh_QtyReceived,
WarehouseQuery.Wh_Location, WarehouseQuery.KitInfo,
WarehouseQuery.Wh_Comments, WarehouseQuery.Wh_Assignment,
WarehouseQuery.Date_received, WarehouseQuery.MFGNumber,
WarehouseQuery.[SupplierNumber], WarehouseQuery.Supplier_ID,
WarehouseQuery.Use_On_Boat, WarehouseQuery.Date_Ordered,
WarehouseQuery.PONumber, WarehouseQuery.Price_Per_Unit,
WarehouseQuery.COMMENTS, WarehouseQuery.Thickness_Width,
WarehouseQuery.Length, WarehouseQuery.TaskID, WarehouseQuery.JobCode,
WarehouseQuery.Inventory, WarehouseQuery.PHASE

FROM WarehouseQuery

WHERE (((WarehouseQuery.BoatNumber)=[Forms]![QBF_Form]![txtWhatBoat] Or
[Forms]![QBF_Form]![txtWhatBoat] Is Null) AND
((WarehouseQuery.SJCNumber)=[Forms]![QBF_Form]![txtWhatSJCNumber] Or
[Forms]![QBF_Form]![txtWhatSJCNumber] Is Null) AND
((WarehouseQuery.DESCRIPTION)=[Forms]![QBF_Form]![txtWhatDescription] Or
[Forms]![QBF_Form]![txtWhatDescription] Is Null) AND
((WarehouseQuery.MFGNumber)=[Forms]![QBF_Form]![txtMFGNumber] Or
[Forms]![QBF_Form]![txtMFGNumber] Is Null) AND
((WarehouseQuery.[SupplierNumber])=[Forms]![QBF_Form]![txtWhatSupplierNumber
] Or [Forms]![QBF_Form]![txtWhatSupplierNumber] Is Null) AND
((WarehouseQuery.Supplier_ID)=[Forms]![QBF_Form]![txtWhatSupplier] Or
[Forms]![QBF_Form]![txtWhatSupplier] Is Null) AND
((WarehouseQuery.PONumber)=[Forms]![QBF_Form]![txtWhatPONumber] Or
[Forms]![QBF_Form]![txtWhatPONumber] Is Null) AND
((WarehouseQuery.JobCode)=[Forms]![QBF_Form]![txtWhatJobCode] Or
[Forms]![QBF_Form]![txtWhatJobCode] Is Null));



The text boxes can be left null or can have a value to determine what shows
up in the sub form. Sometimes it works fine and other times I get a message
that the query is too long and will be trunkated or the query is too
complex. Does anyone have any ideas what can be done to get consistant
results.



Thanks for any help,

Ron
Click to expand...
 
R

Ron DeGregorio

Does it appear to you that my code anywhere near approaches any of these
limitation? Which ones would they be?

Ron

Jerry Whittle said:
Here's the Access query limitations:
Number of enforced relationships: 32 per table minus the number of indexes
that are on the table for fields or combinations of fields that are not
involved in relationships
Number of tables in a query: 32
Number of fields in a recordset: 255
Recordset size: 1 gigabyte
Sort limit: 255 characters in one or more fields
Number of levels of nested queries: 50
Number of characters in a cell in the query design grid: 1,024
Number of characters for a parameter in a parameter query: 255
Number of ANDs in a WHERE or HAVING clause: 99
Number of characters in an SQL statement: approximately 64,000

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Ron said:
I took both suggestions and came up with the following code:

WQ.Wh_Date_received, WQ.Wh_QtyReceived, WQ.Wh_Location,
WQ.KitInfo, WQ.Wh_Comments, WQ.Wh_Assignment, WQ.Date_received,
WQ.MFGNumber, WQ.[SupplierNumber], WQ.Supplier_ID,
WQ.Use_On_Boat, WQ.Date_Ordered, WQ.PONumber, WQ.Price_Per_Unit,
WQ.COMMENTS, WQ.Thickness_Width, WQ.Length, WQ.TaskID,
WQ.JobCode, WQ.Inventory, WQ.PHASE

FROM WarehouseQuery AS WQ

WHERE ( ( WQ.BoatNumber=[Forms]![Q]![txBoat] )
Or ( [Forms]![Q]![txBoat] Is Null ) )
AND ( ( WQ.SJCNumber=[Forms]![Q]![txSJCN] )
Or ( [Forms]![Q]![tx SJCN] Is Null ) )
AND ( (WQ.DESCRIPTION=[Forms]![Q]![txDesc] )
Or ( [Forms]![Q]![txDesc] Is Null ) )
AND ( ( WQ.MFGNumber=[Forms]![Q]![txMFG] )
Or ( [Forms]![Q]![txMFG] Is Null ) )
AND ( (
WQ.[SupplierNumber]=[Forms]![Q]![txSupN] )
Or ( [Forms]![Q]![txSupN] Is Null ) )
AND ( (WQ.Supplier_ID=[Forms]![Q]![txSup] )
Or ( [Forms]![Q]![txSup] Is Null ) )
AND ( ( WQ.PONumber=[Forms]![Q]![txPO] )
Or ([Forms]![Q]![txPO] Is Null ) )
AND ( ( WQ.JobCode=[Forms]![Q]![txJC])
Or ( [Forms]![Q]![txJC] Is Null ) );

The forms are working but I still get messages about complexity being too
great but I can live with that as long as it only affects trying to edit
things. I am really surprised that the complexity was a function of the
number of characters in the code. It never occured to me. Is there an upper
limit to the number of characters in a SQL statement?


Ron said:
I have developed a SQL statement to coordinate a subform with 8 text boxes
in a search form with the following code:

SELECT WarehouseQuery.ID, WarehouseQuery.BoatNumber,
WarehouseQuery.SJCNumber, WarehouseQuery.QTY, WarehouseQuery.DESCRIPTION,
WarehouseQuery.Wh_Date_received, WarehouseQuery.Wh_QtyReceived,
WarehouseQuery.Wh_Location, WarehouseQuery.KitInfo,
WarehouseQuery.Wh_Comments, WarehouseQuery.Wh_Assignment,
WarehouseQuery.Date_received, WarehouseQuery.MFGNumber,
WarehouseQuery.[SupplierNumber], WarehouseQuery.Supplier_ID,
WarehouseQuery.Use_On_Boat, WarehouseQuery.Date_Ordered,
WarehouseQuery.PONumber, WarehouseQuery.Price_Per_Unit,
WarehouseQuery.COMMENTS, WarehouseQuery.Thickness_Width,
WarehouseQuery.Length, WarehouseQuery.TaskID, WarehouseQuery.JobCode,
WarehouseQuery.Inventory, WarehouseQuery.PHASE

FROM WarehouseQuery

WHERE (((WarehouseQuery.BoatNumber)=[Forms]![QBF_Form]![txtWhatBoat] Or
[Forms]![QBF_Form]![txtWhatBoat] Is Null) AND
((WarehouseQuery.SJCNumber)=[Forms]![QBF_Form]![txtWhatSJCNumber] Or
[Forms]![QBF_Form]![txtWhatSJCNumber] Is Null) AND
((WarehouseQuery.DESCRIPTION)=[Forms]![QBF_Form]![txtWhatDescription] Or
[Forms]![QBF_Form]![txtWhatDescription] Is Null) AND
((WarehouseQuery.MFGNumber)=[Forms]![QBF_Form]![txtMFGNumber] Or
[Forms]![QBF_Form]![txtMFGNumber] Is Null) AND
Click to expand...
((WarehouseQuery.[SupplierNumber])=[Forms]![QBF_Form]![txtWhatSupplierNumber
] Or [Forms]![QBF_Form]![txtWhatSupplierNumber] Is Null) AND
((WarehouseQuery.Supplier_ID)=[Forms]![QBF_Form]![txtWhatSupplier] Or
[Forms]![QBF_Form]![txtWhatSupplier] Is Null) AND
((WarehouseQuery.PONumber)=[Forms]![QBF_Form]![txtWhatPONumber] Or
[Forms]![QBF_Form]![txtWhatPONumber] Is Null) AND
((WarehouseQuery.JobCode)=[Forms]![QBF_Form]![txtWhatJobCode] Or
[Forms]![QBF_Form]![txtWhatJobCode] Is Null));



The text boxes can be left null or can have a value to determine what shows
up in the sub form. Sometimes it works fine and other times I get a message
that the query is too long and will be trunkated or the query is too
complex. Does anyone have any ideas what can be done to get consistant
results.



Thanks for any help,

Ron
Click to expand...
Click to expand...
Click to expand...
 
V

Van T. Dinh

We don't know since you have another Query involved, i.e. WarehouseQuery
which we haven't seen.

However, it is rare that you read these limits using the SQL. In the QBE,
the only one I occasionally got stuck with is:

Number of characters in a cell in the query design grid: 1,024

since I use SubQueries a fair bit ...

I did write in my first reply:

It may / may not help the consistency but will make it easier to read by
using Alias and remove some of the parentheses.
Click to expand...

since I think a 64K SQL String is just simply impossible to reach.



I guess you can try the following:

1. Open the WareHouseQuery by itself and see if the WareHouseQuery gives
the same message. If it does, then you need to concentrate on the
WarehouseQuery and not the SQL you posted.

If WareHouseQuery is fine:

2. Since the problem does not occur consistently, I think the problem is in
the (Parameter) input. I am still guessing that there may be some problem
with the automatic type-casting of the Parameters which I wrote one of the
earlier reply. You can try declaring the Parameters and their data-types
explicitly and see if this solves the problem.
 
Top