Query using text from form or anything if empty

[Wes]

Hello,

I have a form that I can input information in several different text boxes,WorkOrder, PurchaseOrder, etc. When I click a search button, it runs a macro that looks at the form and if a box is not empty, it runs that filter. on the output.

I am trying to create one filter that will put the text from the text box in the appropriate column of the filter if the text box has data, but if notit will give ALL records. I thought you could use a "*", but can't seem to get it to work.

Can anyone help?

Here is an example of one of the filters' code. I am trying to combine allthe filters into one single filter to clean up the database.

Thank you for the help.

SELECT PO_tbl_MasterWorkOrder.PurchaseOrder, PO_tbl_MasterWorkOrder.CustomerPO, PO_tbl_MasterWorkOrder.Customer, PO_tbl_MasterWorkOrder.Factory, PO_tbl_MasterWorkOrder.Status, PO_tbl_MasterWorkOrder.CPOFactoryConfirmedXF, PO_tbl_MasterWorkOrder.CPODateFactoryConfirmed, PO_tbl_MasterWorkOrder.CPODateXFConfirmedtoCustomer, PO_tbl_MasterWorkOrder.RevisedXFDate, PO_tbl_MasterWorkOrder.RevisionComment, PO_tbl_MasterWorkOrder.BarcodesReceived, PO_tbl_MasterWorkOrder.BarcodesSentToRO, PO_tbl_MasterWorkOrder.ScansReceived, PO_tbl_MasterWorkOrder.ScansApproved, PO_tbl_MasterWorkOrder.ScansApprovalToRO,PO_tbl_MasterWorkOrder.BarcodesComment, PO_tbl_MasterWorkOrder.ID
FROM PO_tbl_MasterWorkOrder
WHERE (((PO_tbl_MasterWorkOrder.CustomerPO)=[forms]![frmPO_WorkOrderMain]![Cust_POSearch]));

 

[John W. Vinson]

Hello,

I have a form that I can input information in several different text boxes, WorkOrder, PurchaseOrder, etc. When I click a search button, it runs a macro that looks at the form and if a box is not empty, it runs that filter. on the output.

I am trying to create one filter that will put the text from the text box in the appropriate column of the filter if the text box has data, but if not it will give ALL records. I thought you could use a "*", but can't seem to get it to work.

Can anyone help?

Here is an example of one of the filters' code. I am trying to combine all the filters into one single filter to clean up the database.

Thank you for the help.

SELECT PO_tbl_MasterWorkOrder.PurchaseOrder, PO_tbl_MasterWorkOrder.CustomerPO, PO_tbl_MasterWorkOrder.Customer, PO_tbl_MasterWorkOrder.Factory, PO_tbl_MasterWorkOrder.Status, PO_tbl_MasterWorkOrder.CPOFactoryConfirmedXF, PO_tbl_MasterWorkOrder.CPODateFactoryConfirmed, PO_tbl_MasterWorkOrder.CPODateXFConfirmedtoCustomer, PO_tbl_MasterWorkOrder.RevisedXFDate, PO_tbl_MasterWorkOrder.RevisionComment, PO_tbl_MasterWorkOrder.BarcodesReceived, PO_tbl_MasterWorkOrder.BarcodesSentToRO, PO_tbl_MasterWorkOrder.ScansReceived, PO_tbl_MasterWorkOrder.ScansApproved, PO_tbl_MasterWorkOrder.ScansApprovalToRO, PO_tbl_MasterWorkOrder.BarcodesComment, PO_tbl_MasterWorkOrder.ID
FROM PO_tbl_MasterWorkOrder
WHERE (((PO_tbl_MasterWorkOrder.CustomerPO)=[forms]![frmPO_WorkOrderMain]![Cust_POSearch]));

If you're just filtering on one (or maybe up to three or four) fields you can
use a syntax like:

WHERE (PO_tbl_MasterWorkOrder.CustomerPO =
[forms]![frmPO_WorkOrderMain]![Cust_POSearch] OR
[forms]![frmPO_WorkOrderMain]![Cust_POSearch] IS NULL);

This gets messy if you have more than a very few fields, however; going from
SQL view into the design grid will scramble your query into
incomprehensibility. You would do better to write VBA code to loop through the
criteria controls on your form and construct a WhereCondition on the fly.

Do note that this Usenet newsgroup has been abandoned by Microsoft; it's
living on in a zombie-like undeath. See my .sig for more active venues.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Wes]

Thanks John. I'll give that a try.