Multiple Parameters (I'm SOL with SQL)

[irishblue]

I am trying to strengthen an already developed database at my work. They had a form with five different text boxes, each one that would run a seperate query on the same table, and the results would open on a different page. I am trying to combine all these text search parameters from the form to a single query and have the result come up on the form. I have done every search I can thing of from a forum search, a google search, and just reading through query forum posts for about a day; but I still cannot tell what is wrong. If someone could look at my code, and see if I have a mistake, or if I'm even in the right direction.

SELECT FLIGHTS.[FINISH OD], FLIGHTS.[PIPE OD/SHAFT OD], FLIGHTS.PITCH, FLIGHTS.[TYPE OF MATERIAL], FLIGHTS.[BURNED OD], FLIGHTS.[BURNED ID], FLIGHTS.[RIGHT/LEFT HAND], FLIGHTS.PartNumber, FLIGHTS.COMMENTS, FLIGHTS.[START TIME], FLIGHTS.[FINISH TIME], FLIGHTS.PurchaseOrderNumber, FLIGHTS.QTY
FROM FLIGHTS
WHERE ((FLIGHTS.[FINISH OD]=Forms![KNOWN BURN SIZES]!Text25 & "*") And (FLIGHTS.PITCH=Forms![KNOWN BURN SIZES]!text37 & "*") And (FLIGHTS.[TYPE OF MATERIAL]=Forms![KNOWN BURN SIZES]!Text32 & "*") And (FLIGHTS.PartNumber=Forms![KNOWN BURN SIZES]!Text0 & "*") And (FLIGHTS.PurchaseOrderNumber=Forms![KNOWN BURN SIZES]!text36 & "*"));

The FLIGHTS is the main table, [KNOWN BURN TABLES] is the form that the parameters are entered. I want users to be able to enter any fields they want and leave others blank. Currently all I get is a blank query. Sorry if the answer seems obvious; I tried avoiding posting till I knew that I couldn't figure it out on my own.

Thanks.
-Brian

 

[Len Boorman]

Basically the where clause is a series of "ANDS". This means that the results passed back MUST satisfy ALL of the search parameters.

If some are left blank then it will be looking for nulls in those fields, probably. It can be difficult to set up search on multiple fields because if you use OR you get a mixed bag of results that may not be of much use either.

Previously I have used cascading searches where you search on One Field only and get back a set of results. Then a second search on the first results using a second field and so on. If you simply hold the results within a form and just open subsequent search results it is possible for the user to step back to previuous results and select a new search criteria.

HTH

 

[irishblue]

Thank you Len for your advice. I thought it may have had something to do with null values. I actually tried to get the query to ignore if nul by using "*", something I read on one of the forums. Does anyone know of a way to ignore parameters if they are left null. I would much rather have this be a one step thing for the users.
Thanks,
-Brian

 

[Len Boorman]

Think it is more than the nulls. The And's mean that only those records that satisfy ALL the parameters will be returned.

L

 

[irishblue]

I actually want the query to return only one result, but even when I enter everything for the query that matches one specific result, it still comes up blank.

 

[WayneRyan]

Irish,

When you use the "*" for a wildcard, you must use the Like operator.

[FINISH OD] Like Forms![KNOWN BURN SIZES]!Text25 & "*"

Wayne

 

[irishblue]

Thanks Wayne. After a good deal of trial and error, I figured it out . I got rid of the "*" and inserted a command to accept null values. This is what I ended up with for anyone wonding how to do it Disclaimer: theres a good chance this isn't the best or easiest way, but it worked for me and thats what I needed.

SELECT FLIGHTS.[FINISH OD], FLIGHTS.[PIPE OD/SHAFT OD], FLIGHTS.PITCH, FLIGHTS.[TYPE OF MATERIAL], FLIGHTS.[BURNED OD], FLIGHTS.[BURNED ID], FLIGHTS.[RIGHT/LEFT HAND], FLIGHTS.PartNumber, FLIGHTS.COMMENTS, FLIGHTS.[START TIME], FLIGHTS.[FINISH TIME], FLIGHTS.PurchaseOrderNumber, FLIGHTS.QTY
FROM FLIGHTS
WHERE (((FLIGHTS.[FINISH OD]=Forms![KNOWN BURN SIZES]!Text25) Or (Forms![KNOWN BURN SIZES]!Text25 Is Null)) And ((FLIGHTS.PITCH=Forms![KNOWN BURN SIZES]!text37) Or (Forms![KNOWN BURN SIZES]!text37 Is Null)) And ((FLIGHTS.[TYPE OF MATERIAL]=Forms![KNOWN BURN SIZES]!Text29) Or (Forms![KNOWN BURN SIZES]!Text29 Is Null)) And ((FLIGHTS.PartNumber=Forms![KNOWN BURN SIZES]!Text0) Or (Forms![KNOWN BURN SIZES]!Text0 Is Null)) And ((FLIGHTS.PurchaseOrderNumber=Forms![KNOWN BURN SIZES]!text33) Or (Forms![KNOWN BURN SIZES]!text33 Is Null)));

 

[WayneRyan]

Irish,

That looks good. The only comment is that if something is entered in
one of your textboxes, then cleared later, it won't be Null -- it will be
an empty string.

Instead of:

Forms![KNOWN BURN SIZES]!Text25 Is Null

You could try:

Nz(Forms![KNOWN BURN SIZES]!Text25) = ""

Then it's guaranteed to work.

Wayne

 

[irishblue]

Thanks.