Form that feeds a query

[mtrcct]

Hi, I am working with a form that is unbound and has text boxes the user can type in wildcard search critera which feeds a query. If I leave all the text boxes blank all query recoors are returned, however if i put a condition in any of the text boxes on the form and hit the command button to run the query the expected result is not returned. Is there a limit to the number of conditions a query can have going to a form? The sequel is noted below as to show how this is built, seems to almost work and I just can't figure out what the problem is. Any help would be appreciated.

SELECT tblPurchaseOrde rHdr.PO_Number, tblPurchaseOrde rHdr.Buyer, tblPurchaseOrde rHdr.OrderDate, tblPurchaseOrde rHdr.OrderDueDa te, tblPurchaseOrde rHdr.Vendor, tblPurchaseOrde rHdr.VenAddress 1, tblPurchaseOrde rHdr.VenCity, tblPurchaseOrde rHdr.VenState_P rov, tblShipToLocati ons.Name, tblPurchaseOrde rHdr.ShipTo, tblPurchaseOrde rHdr.ShipAddres s1, tblPurchaseOrde rHdr.ShipCity, tblPurchaseOrde rHdr.ShipState_ Prov, tblPurchaseOrde rHdr.ShipZip_Po stCode, tblPurchaseOrde rHdr.ShipContac t, tblPurchaseOrde rHdr.Comments, tblPurchaseOrde rHdr.FreightAnd Insurance, tblPurchaseOrde rHdr.Applicable Taxes, tblPurchaseOrde rHdr.Applicable Tariffs, tblRequisition.[OEM/MFG], tblRequisition. ModelNumber, tblRequisition. ModelPartNumber , tblRequisition. Description, tblRequisition. PO_Qty, tblRequisition. PO_UnitListCost Ea, tblRequisition. PO_UnitNetCostE a, [PO_Qty]*[PO_UnitNetCostE a] AS ItemExtendedCos t, tblPurchaseOrde rHdr.AccountApp ropiationNumber , tblPurchaseOrde rHdr.VendorQuot eRef
FROM (tblPurchaseOrd erHdr LEFT JOIN tblRequisition ON tblPurchaseOrde rHdr.PO_Number = tblRequisition. PO_ItemPONumber ) LEFT JOIN tblShipToLocati ons ON (tblPurchaseOrd erHdr.ShipAddre ss1 = tblShipToLocati ons.Address1) AND (tblPurchaseOrd erHdr.ShipCity = tblShipToLocati ons.City)
WHERE (((tblPurchaseO rderHdr.PO_Numb er) Like "*" & [FORMS]![frmPoCarReviewS elections]![PO_Number] & "*") AND ((tblPurchaseOr derHdr.Buyer) Like "*" & [FORMS]![frmPoCarReviewS elections]![Buyer] & "*") AND ((tblPurchaseOr derHdr.OrderDat e) Like "*" & [FORMS]![frmPoCarReviewS elections]![OrderDate] & "*") AND ((tblPurchaseOr derHdr.OrderDue Date) Like "*" & [FORMS]![frmPoCarReviewS elections]![OrderDueDate] & "*") AND ((tblPurchaseOr derHdr.Vendor) Like "*" & [FORMS]![frmPoCarReviewS elections]![Vendor] & "*") AND ((tblPurchaseOr derHdr.VenAddre ss1) Like "*" & [FORMS]![frmPoCarReviewS elections]![VenAddress1] & "*") AND ((tblPurchaseOr derHdr.VenCity) Like "*" & [FORMS]![frmPoCarReviewS elections]![VenCity] & "*") AND ((tblPurchaseOr derHdr.VenState _Prov) Like "*" & [FORMS]![frmPoCarReviewS elections]![VenState_Prov] & "*") AND ((tblShipToLoca tions.Name) Like "*" & [FORMS]![frmPoCarReviewS elections]![Name] & "*") AND ((tblPurchaseOr derHdr.ShipAddr ess1) Like "*" & [FORMS]![frmPoCarReviewS elections]![ShipAddress1] & "*") AND ((tblPurchaseOr derHdr.ShipCity ) Like "*" & [FORMS]![frmPoCarReviewS elections]![ShipCity] & "*") AND ((tblPurchaseOr derHdr.ShipStat e_Prov) Like "*" & [FORMS]![frmPoCarReviewS elections]![ShipState_Prov] & "*") AND ((tblPurchaseOr derHdr.ShipZip_ PostCode) Like "*" & [FORMS]![frmPoCarReviewS elections]![ShipZip_PostCod e] & "*") AND ((tblRequisitio n.[OEM/MFG]) Like "*" & [FORMS]![frmPoCarReviewS elections]![OEM/MFG] & "*") AND ((tblRequisitio n.ModelNumber) Like "*" & [FORMS]![frmPoCarReviewS elections]![ModelNumber] & "*") AND ((tblRequisitio n.ModelPartNumb er) Like "*" & [FORMS]![frmPoCarReviewS elections]![ModelPartNumber] & "*") AND ((tblRequisitio n.Description) Like "*" & [FORMS]![frmPoCarReviewS elections]![Description] & "*") AND ((tblRequisitio n.PO_Qty) Like "*" & [FORMS]![frmPoCarReviewS elections]![PO_Qty] & "*") AND ((tblRequisitio n.PO_UnitListCo stEa) Like "*" & [FORMS]![frmPoCarReviewS elections]![PO_UnitListCost Ea] & "*") AND ((tblRequisitio n.PO_UnitNetCos tEa) Like "*" & [FORMS]![frmPoCarReviewS elections]![PO_UnitNetCostE a] & "*") AND ((tblPurchaseOr derHdr.AccountA ppropiationNumb er) Like "*" & [FORMS]![frmPoCarReviewS elections]![AccountAppropia tionNumber] & "*") AND ((tblPurchaseOr derHdr.VendorQu oteRef) Like "*" & [FORMS]![frmPoCarReviewS elections]![VendorQuoteRef] & "*"));

[maxamis4]

to answer your first question: No sql does not have a limit. Could you please explain how your user made this query? If you are making a dynamic query then you need to generate it from scratch with only the variables you are using. Example:


YOUR FORM
*************** *************** *************** ****
Listbox1 field1, field2, field3
Listbox2


Button1 (All) = "*"
DropdownList = table1, table2, table3
*************** *************** *************** ****

you selected field1 and field 3 from your list box into listbox2.
Then you clicked on button 1 and then you selected from the dropdown list table1

your result is this query "Select field1, field2 * from table1"

this can all happen in vb. I hope this helps a little.

[mtrcct]

Hi, thanks for the feedback. Actually, I made the query and it simply pulls data from the tables I noted in the sql. I have the query going to my form to receive the imput from the user and it is supposed to perform a wildcard search on the fields noted. The form is unbound and contains all unbound text boxes, nothing fancy, so after the selections are made there is a command button on the form that runs the query I described.

I don't get erros, but it does not bring back the complete record set it should, almost like there is something else filtering it....What do you think???

to answer your first question: No sql does not have a limit. Could you please explain how your user made this query? If you are making a dynamic query then you need to generate it from scratch with only the variables you are using. Example:


YOUR FORM
*************** *************** *************** ****
Listbox1 field1, field2, field3
Listbox2


Button1 (All) = "*"
DropdownList = table1, table2, table3
*************** *************** *************** ****

you selected field1 and field 3 from your list box into listbox2.
Then you clicked on button 1 and then you selected from the dropdown list table1

your result is this query "Select field1, field2 * from table1"

this can all happen in vb. I hope this helps a little.

[mtrcct]

Hi, before anyone spends any more time on this I got the query to operate properly. After review of the first reply sent to me I regarding any limits the sql could have, I went back and cleared all the code in the query and added each statement in one at a time and now it works fine. I never found the problem, the only thing I can think of is there may have been some embedded characters in a few statements since I coppied and pasted on the first go around and then went back to change the control names.

Thanks for the feedback...It did help me out...