SQL multiple where clause AND/OR

[Misiek]

Hello,

I have a form with 2 cbo. I want query to show all data if nothing has been selected, or show specific data to whatever has been selected in cbo1 AND/OR cbo2.

My query was working ok for couple of days and then stopped. I don't know why, as no changes has been made.

if both cbo are blank then it works OR if both cbo have a selected value then it works, ok. But it doesn't if one of the cbo has a value and the other one hasn't.

I believe the problem is somewhere here:

WHERE (dAreaFK=Forms!F_MainMenu.cboStatsArea 
AND dShiftFK=Forms!F_MainMenu.cboStatsShift) 
OR (Forms!F_MainMenu!cboStatsArea IS NULL 
AND Forms!F_MainMenu!cboStatsShift IS NULL)

thank you

 

[jdraw]

It seems to me you are excluding the case when only one cbo has a value.
Try this

WHERE
 (dAreaFK=Forms!F_MainMenu.cboStatsArea 
AND dShiftFK=Forms!F_MainMenu.cboStatsShift) 
OR 
(Forms!F_MainMenu!cboStatsArea IS NULL 
[COLOR="Purple"][B]OR[/B][/COLOR] Forms!F_MainMenu!cboStatsShift IS NULL)

 

[Misiek]

just tried it, it shows the same results no matter if cboStatsArea has been selected or not, and doesn't recognise value between selections.
:/

 

[plog]

You need a much more complex criteria statement. You have 4 different scenarios to catch:

A: Box1 Null, Box2 Null
B: Box1 Null, Box2 Used
C: Box 1 Used, Box2 Null
D: Box 1 Used, Box2 Used

That means you need 4 different 'parts' of your WHERE statement, each comprising both inputs:

WHERE (A) OR (B) OR (C) OR (D)

 

[Brianwarnock]

Try
WHERE (dAreaFK=Forms!F_MainMenu.cboStatsArea
OR Forms!F_MainMenu!cboStatsArea IS NULL)
AND (dShiftFK=Forms!F_MainMenu.cboStatsShift
OR Forms!F_MainMenu!cboStatsShift IS NULL)

Brian

 

[namliam]

Try it the "proper" way, if you dont want to search for something dont search it.

I.e. in your form, build your SQL to suite exactly the need you have at the time
Either/or
1)
WHERE dAreaFK=Forms!F_MainMenu.cboStatsArea
AND dShiftFK=Forms!F_MainMenu.cboStatsShift
2)
WHERE dAreaFK=Forms!F_MainMenu.cboStatsArea
3)
WHERE dShiftFK=Forms!F_MainMenu.cboStatsShift
4)
No where clause

To fill a query object with sql you want something like
Currentdb.Querydefs("YourQueryName").sql = "Select ..."

 

[Misiek]

Plog,

I tried this way, already, and whether I make a selection or not, results are exactly the same.

Brian,
This works lovely, Thank you
I Did try it before writing a post here, but I believe my brackets were in different places, perhaps thats why it didn't work in first place.

Many thanks to all for your input

 

[Brianwarnock]

Yep the bracketing is important.

Glad to have helped

Brian

BTW that code is bog standard multiple criteria search when flexible choice of the criteria is require

(myfield=criteria or criteria Is Null)
And ( etc for every field

But Note never go back to the design grid view once you have done this as the GUI makes a real mess of it.