Unanswered: parameter query for range or shows all
I'm pulling my hair out on this one...
I am fairly new to all this stuff but I have put in the hours on this and I can't seem to get through this roadblock:
I have created a form (frmSearch) in which the end user will enter criteria to search for in a table (tblPart). The user clicks on a search button and the matching records are displayed in a subform (subfrmSearch). There are multiple text boxes on frmSearch in which the user can search up to 10 different fields in tblPart - for example part number, supplier, etc. It is not required for all text boxes on the frmSearch to be filled by the user. If nothing is entered into the text box, all records need to be returned. For example, if the user enters part or all of a supplier name, and no part number, the query will return records from this supplier, irregardless of the part number.
Also, the tblPart has fields for the mid spec valve for hardness for each part and a range associated with it. For example, the hardness for a specific part is 250 newtons and the range is 10 newtons. In this case the low spec is 250 - 10 = 240 and the high is 260. I need to be able to perform a query, using a value entered by the user on the frmSearch, that will find all parts that are in the range of the low and high spec value. e.g. if the user entered 245, the query will select the above record as 240 < 245 < 260. Again, if no value is entered into the text box, all records should be returned that meet other (if any) criteria entered by the user.
Any help would be appreciated - thanks to all for this great resource!!!
Here is sql that I use for a report.
Parameter asks for a name - if the parameter is left blank, it returns ALL records.
Can you use something like this?
FROM tblPaymentPosting INNER JOIN tblEmployee ON tblPaymentPosting.EmployeeName = tblEmployee.[EMPLOYEE NAME]
WHERE (((tblPaymentPosting.EmployeeName)=[Enter a Name Leave blank to show all records])) OR ((([Enter a Name Leave blank to show all records]) Is Null));