By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,588 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Form that feeds a query

P: 19
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 tblPurchaseOrderHdr.PO_Number, tblPurchaseOrderHdr.Buyer, tblPurchaseOrderHdr.OrderDate, tblPurchaseOrderHdr.OrderDueDate, tblPurchaseOrderHdr.Vendor, tblPurchaseOrderHdr.VenAddress1, tblPurchaseOrderHdr.VenCity, tblPurchaseOrderHdr.VenState_Prov, tblShipToLocations.Name, tblPurchaseOrderHdr.ShipTo, tblPurchaseOrderHdr.ShipAddress1, tblPurchaseOrderHdr.ShipCity, tblPurchaseOrderHdr.ShipState_Prov, tblPurchaseOrderHdr.ShipZip_PostCode, tblPurchaseOrderHdr.ShipContact, tblPurchaseOrderHdr.Comments, tblPurchaseOrderHdr.FreightAndInsurance, tblPurchaseOrderHdr.ApplicableTaxes, tblPurchaseOrderHdr.ApplicableTariffs, tblRequisition.[OEM/MFG], tblRequisition.ModelNumber, tblRequisition.ModelPartNumber, tblRequisition.Description, tblRequisition.PO_Qty, tblRequisition.PO_UnitListCostEa, tblRequisition.PO_UnitNetCostEa, [PO_Qty]*[PO_UnitNetCostEa] AS ItemExtendedCost, tblPurchaseOrderHdr.AccountAppropiationNumber, tblPurchaseOrderHdr.VendorQuoteRef
FROM (tblPurchaseOrderHdr LEFT JOIN tblRequisition ON tblPurchaseOrderHdr.PO_Number = tblRequisition.PO_ItemPONumber) LEFT JOIN tblShipToLocations ON (tblPurchaseOrderHdr.ShipAddress1 = tblShipToLocations.Address1) AND (tblPurchaseOrderHdr.ShipCity = tblShipToLocations.City)
WHERE (((tblPurchaseOrderHdr.PO_Number) Like "*" & [FORMS]![frmPoCarReviewSelections]![PO_Number] & "*") AND ((tblPurchaseOrderHdr.Buyer) Like "*" & [FORMS]![frmPoCarReviewSelections]![Buyer] & "*") AND ((tblPurchaseOrderHdr.OrderDate) Like "*" & [FORMS]![frmPoCarReviewSelections]![OrderDate] & "*") AND ((tblPurchaseOrderHdr.OrderDueDate) Like "*" & [FORMS]![frmPoCarReviewSelections]![OrderDueDate] & "*") AND ((tblPurchaseOrderHdr.Vendor) Like "*" & [FORMS]![frmPoCarReviewSelections]![Vendor] & "*") AND ((tblPurchaseOrderHdr.VenAddress1) Like "*" & [FORMS]![frmPoCarReviewSelections]![VenAddress1] & "*") AND ((tblPurchaseOrderHdr.VenCity) Like "*" & [FORMS]![frmPoCarReviewSelections]![VenCity] & "*") AND ((tblPurchaseOrderHdr.VenState_Prov) Like "*" & [FORMS]![frmPoCarReviewSelections]![VenState_Prov] & "*") AND ((tblShipToLocations.Name) Like "*" & [FORMS]![frmPoCarReviewSelections]![Name] & "*") AND ((tblPurchaseOrderHdr.ShipAddress1) Like "*" & [FORMS]![frmPoCarReviewSelections]![ShipAddress1] & "*") AND ((tblPurchaseOrderHdr.ShipCity) Like "*" & [FORMS]![frmPoCarReviewSelections]![ShipCity] & "*") AND ((tblPurchaseOrderHdr.ShipState_Prov) Like "*" & [FORMS]![frmPoCarReviewSelections]![ShipState_Prov] & "*") AND ((tblPurchaseOrderHdr.ShipZip_PostCode) Like "*" & [FORMS]![frmPoCarReviewSelections]![ShipZip_PostCode] & "*") AND ((tblRequisition.[OEM/MFG]) Like "*" & [FORMS]![frmPoCarReviewSelections]![OEM/MFG] & "*") AND ((tblRequisition.ModelNumber) Like "*" & [FORMS]![frmPoCarReviewSelections]![ModelNumber] & "*") AND ((tblRequisition.ModelPartNumber) Like "*" & [FORMS]![frmPoCarReviewSelections]![ModelPartNumber] & "*") AND ((tblRequisition.Description) Like "*" & [FORMS]![frmPoCarReviewSelections]![Description] & "*") AND ((tblRequisition.PO_Qty) Like "*" & [FORMS]![frmPoCarReviewSelections]![PO_Qty] & "*") AND ((tblRequisition.PO_UnitListCostEa) Like "*" & [FORMS]![frmPoCarReviewSelections]![PO_UnitListCostEa] & "*") AND ((tblRequisition.PO_UnitNetCostEa) Like "*" & [FORMS]![frmPoCarReviewSelections]![PO_UnitNetCostEa] & "*") AND ((tblPurchaseOrderHdr.AccountAppropiationNumber) Like "*" & [FORMS]![frmPoCarReviewSelections]![AccountAppropiationNumber] & "*") AND ((tblPurchaseOrderHdr.VendorQuoteRef) Like "*" & [FORMS]![frmPoCarReviewSelections]![VendorQuoteRef] & "*"));
Jun 1 '07 #1
Share this Question
Share on Google+
3 Replies


maxamis4
Expert 100+
P: 295
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.
Jun 1 '07 #2

P: 19
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.
Jun 4 '07 #3

P: 19
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...
Jun 4 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.