Multiple query criteria in form 1

[ProdS]

Hi,

I am currently doing a database project for school. I have a form called "advflight" which has 2 list boxes, 2 text boxes and 3 combo-boxes. The problem is that when i select one value from a box to be my criteria, it accepts all the other values as nothing, therefore returning no results in my query. But i need all these fields considered in the query UNLESS no value has been selected from them. Any help with this would be appreciated

 

[lupins46]

How are you running your query (what causes it to run?)

 

[PHV]

The usual way, in the criteria cell of the query grid:
=[Forms]![mainform]![control] Or [Forms]![mainform]![control] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ProdS]

Thanks, but i tried that last one but it didnt help, if i selected nothing in the form, it successfully showed all records, but not when i made a specific selection, it still turned up no results
this is what i have in the form

Private Sub Command29_Click()
DoCmd.OpenQuery "advflightsearch"
Me.arriving = ""
Me.gate = ""
Me.plID = ""
Me.departing = ""
Me.darr = ""
Me.ddep = ""
Me.Airline = ""
End Sub

is there something wrong there?

 

[PHV]

You may this instead:
=[Forms]![mainform]![control] Or Trim([Forms]![mainform]![control] & "")=""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ProdS]

No luck, i get a "You cancelled the previous operation" error, when i debug, it highlights

Private Sub Command29_Click()
DoCmd.OpenQuery "advflightsearch" <--whole line highlighted
Me.arriving = ""
Me.gate = ""
Me.plID = ""
Me.departing = ""
Me.darr = ""
Me.ddep = ""
Me.Airline = ""
End Sub

 

[PHV]

What happens if you set the desired criteria in your form and then launch the query from the database window (instead of click Command29 button) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ProdS]

same thing again, no records in my query

There definetely should be, i tried multiple values in all fields but i still got no results

However, when i entered data into the form which was exactly the same as one record in my table, it successfully showed that one. Not sure if that helps.

 

[PHV]

Can you please post the WHERE clause of advflightsearch's SQL code saying us which criteria is numeric, date or text ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ProdS]

WHERE ((([Arrivals/Departures].[Plane ID])=[Forms]![advflight]![plID]) AND (([Arrivals/Departures].[Gate Number])=[Forms]![advflight]![gate]) AND (([Arrivals/Departures].[Arriving From])=[Forms]![advflight]![arriving]) AND (([Arrivals/Departures].[Date of Arrival])=[Forms]![advflight]![darr]) AND (([Arrivals/Departures].[Date of Departure])=[Forms]![advflight]![ddep]) AND (([Arrivals/Departures].Airline)=[Forms]![advflight]![airline]) AND (([Arrivals/Departures].[Departing To])=[Forms]![advflight]![departing])) OR (((Trim([Forms]![advflight]![plID] & ""))="") AND ((Trim([Forms]![advflight]![gate] & ""))="") AND ((Trim([Forms]![advflight]![arriving] & ""))="") AND ((Trim([Forms]![advflight]![darr] & ""))="") AND ((Trim([Forms]![advflight]![ddep] & ""))="") AND ((Trim([Forms]![advflight]![airline] & ""))="") AND ((Trim([Forms]![advflight]![departing] & ""))=""));

thats it in SQL

"Plane ID" is an autonumber
"Gate Number" is a number
"arriving from", "departing to", "status arrival", "status departure", "Flight ID Arrive", "Flight ID Depart" and "airline" are all text
"ETA", "ETD", "Date of Arrival", "Date of Departure" are all Date & Time

The only ones in the form are:
Plane ID
Gate Number
Arriving From
Departing To
Date of Arrival
Date of Departure
Airline

Is that wat u wanted?

 

[PHV]

You may this WHERE clause instead:
WHERE ([Arrivals/Departures].[Plane ID]=[Forms]![advflight]![plID]
OR Trim([Forms]![advflight]![plID] & "")="")
AND ([Arrivals/Departures].[Gate Number]=[Forms]![advflight]![gate]
OR Trim([Forms]![advflight]![gate] & "")="")
AND ([Arrivals/Departures].[Arriving From]=[Forms]![advflight]![arriving]
OR Trim([Forms]![advflight]![arriving] & "")="")
AND ([Arrivals/Departures].[Date of Arrival]=[Forms]![advflight]![darr]
OR Trim([Forms]![advflight]![darr] & "")="")
AND ([Arrivals/Departures].[Date of Departure]=[Forms]![advflight]![ddep]
OR Trim([Forms]![advflight]![ddep] & "")="")
AND ([Arrivals/Departures].Airline=[Forms]![advflight]![airline]
OR Trim([Forms]![advflight]![departing] & "")="")
AND ([Arrivals/Departures].[Departing To]=[Forms]![advflight]![departing]
OR Trim([Forms]![advflight]![airline] & "")="")
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ProdS]

Sooooooo close!!
I opened the form and selected one and bingo! it worked! But when i closed the result table and clicked on another selection, the error "You cancelled the previous operation" came up. What could be causing that?

 

[PHV]

Some wrong logic in your form events processing.
BTW, a parameter form is rarely bound.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ProdS]

all my objects are unbound though, there is no record source in the form

 

[ProdS]

Thanks, but i tried that last one but it didnt help, if i selected nothing in the form, it successfully showed all records, but not when i made a specific selection, it still turned up no results, I'll try it again though

 

[FancyPrairie]

You might want to look at this FAQ faq181-5497. It contains a function that will return the Where clause for you without the word Where. It works for single and multi-select list boxes, combo boxes, text boxes, date ranges, option groups, and check boxes. Note you don't have to understand what the code is doing, just call the function. The header within the function explains how to use it. The problem some people have when using the function for the first time is that they don't set their tag property correctly.