Using AND & OR Operators in SQL query Criteria to make choices

[Jerry Maiapu]

Hello Everyone,
I have 3 combos on a form (budled with- year, quarter and month)that users will use them as filters to run a report.
What I want is to use the OR & AND operators in the query as the record source to generate report based on values selected on the 3 combos. I also have the 3 corresponding columns names in my query.
Any ideas in way forward is appreciated.

This is a snap shot query of what am using:

1st attempt:

Select  fld_year, fld_qrter, fld_month,
From Table1
Where fld_year=[year field from form] OR  fld_ qrter =[quarter field from form] OR fld_month=[month field from form];

2nd Attmenpt-using iif:

Select  fld_year, fld_qrter, fld_month,
From Table1
Where iif(isnull([quarter field from form] fld_year=[year field from form],iif(isnull([month field from form]), fld_ qrter =[quarter field from form], fld_month=[month field from form]));

Note: On the form, the year combo field is required whilst the three months is automatically populated after quarter is selected from the quarter combo;

What I want to achieve is:
(1)if only year is selected in the combo box then the query should filter records for year only else
(2)if Year and Quarter selected then display for that quarter in that year only else
(3) if Month is selected then display query results for that month of the year only


Thanks..

[Anas Mosaad]

Your first attempt will return results in any year or quarter or month selected. Here's a snippet that hopefully solves your problem.

where 
fld_year=[year field from form] AND
(
(isnull([month field from form]) and isnull([quarter field from form])
OR 
(isnull([month field from form]) and fld_ qrter =[quarter field from form])
OR
(fld_month=[month field from form]) and fld_ qrter =[quarter field from form])
)

I'm not sure Using IIF could fit in this case. Hopefully the above solution is enough.

[NeoPa]

The first thing I suggest you do Jerry, is to handle the form such that when either B or C has a selection, the other is disabled. When a selection is cleared this must also trigger re-enabling the other of course. Also, from your 1,2,3 explanation (though not from the rest unfortunately), it seems you also need the Year value specified in all circumstances. If that is true, then use then take similar steps to disable and re-enable both B and C depending on A.

Once you have your form constructed correctly you can then run the code that will build up your SQL string. It will always include :

strWhere = "([fld_Year]=#%A#)"

Where %A is determined by calling :

Format(CDate([Year Field from Form], "m\/d\/yyyy")

If either of the other two controls has a value then you need to append the following string to strWhere :

strWhere = strWhere & " AND ([fld_ Qrter]=[Quarter Field from Form])"

This example shows the Quarter format. Only one should ever be added to the string.

Let us know how you get on with this. If you have trouble with it please post what you've tried and what the issues were (in full and clear detail) and we'll help you further from there.

[Jerry Maiapu]

Thanks Anas Mosaad and NeoPa both for your responses. In fact I combined part of both of your suggestions.

I have handled the form data entry using NeoPa's approach and used Anas Mosaad suggestion to pull filtered data off the query running the report.

NeoPa, I have also on the other hand tried your suggestion and worked as well so thanks Neo for your continues assistance.

Since am using Anas Mosaad suggestion..I just decided to choose his post as the best answer..if there was an option to choose two posts as best answers then I would have done so..

Thanks again both...

[NeoPa]

You're very welcome Jerry. Don't worry about the Best Answer. I don't feel remotely disappointed. I get plenty anyway and understand as well as anyone that only one is available. It makes more sense to encourage a new poster than to worry about an old hand like me.

[Anas Mosaad]

@Jerry: I'm glad that you have a working solution.

@NeoPa: that a great spirit that is rare nowadays though I'm not caring about how much best answer I get and honestly I'm not tracking. I believe it doesn't make a difference as long as the person shows that your reply helped - even a bit. I'm trying to help people on my own and I'm not aiming to get rewarded for this. My only reward is that I participated in resolving someone's issue.

[NeoPa]

Anas:
I believe it doesn't make a difference as long as the person shows that your reply helped - even a bit

Amen to that brother!

None of the rest hurts, but a polite OP makes all the difference for me. Not that it's absolutely necessary for my enjoyment, as I know all these threads are viewed by hundreds, and sometimes many more, of others with similar problems. Knowing I've helped is my enjoyment.

[Anas Mosaad]

I agree with your point Adrian, thanks.