Options Group and Query Criteria, revisited

[rocketD]

I recently posted a question about option groups passing info to a
query for a Yes/No field, and received some great advice. However,
I've tried to apply that advice in a similar way to a Date field, to
no avail. In the query, I want the user to be able to display (1) only
records with dates present or (2) only records without dates present
or (3) all records.

I changed the query criterion that worked for the checkbox as follows,
and placed it under the Date field which I want to select by (this
statement yields no records):

IIF([forms].[fmQryByClinicSurveys].[emailOptions]=1,(([tblTracking].
[Received]) Is Not Null), IIF([forms].[fmQryByClinicSurveys].
[emailOptions]=2,(([tblTracking].[Received]) Is Null),Null)) OR
[forms].[fmQryByClinicSurveys].[emailOptions]=3

The [emailOptions] control is the name of the option group frame on
the form. When I use Is Null or Is Not Null alone under the date
field, I get displays (1) and (2). I just don't know how to translate
this into my SQL statement.

Thanks for your help, I thought I understood what I was doing.
Dara

 

[rocketD]

Dara:

You should be able do this by using OR operations on parenthesised AND
operations:

SELECT *
FROM tblTracking
WHERE
(Forms!fmQryByClinicSurveys!emailOptions = 1
AND Received IS NOT NULL)
OR
(Forms!fmQryByClinicSurveys!emailOptions = 2
AND Received IS NULL)
OR
Forms!fmQryByClinicSurveys!emailOptions = 3;

So:

1.  If the value of the option group is 1 and the Received column is not Null,
the WHERE clause will evaluate to TRUE and the row will be returned.
Or:
2.  If the value of the option group is 2 and the Received column is Null,
the WHERE clause will evaluate to TRUE and the row will be returned.
Or:
3.  If the value of the option group is 3, the WHERE clause will evaluate to
TRUE regardless, i.e. all rows will be returned.

As well as handling alternative parameters like this you can also use similar
logic to optionalize parameters, but in that case its AND operations on
parenthesised OR operations.  This will generally be better that calling the
IIF function as to extend the number of optional parameters  its merelya
case of tacking on another parenthesised expression, whereas with an IIF
function you could soon end up with nested function calls of Byzantine
proportions to cope with all the permutations.

Ken Sheridan
Stafford, England

I recently posted a question about option groups passing info to a
query for a Yes/No field, and received some great advice.  However,
I've tried to apply that advice in a similar way to a Date field, to
no avail. In the query, I want the user to be able to display (1) only
records with dates present or (2) only records without dates present
or (3) all records.

I changed the query criterion that worked for the checkbox as follows,
and placed it under the Date field which I want to select by (this
statement yields no records):

IIF([forms].[fmQryByClinicSurveys].[emailOptions]=1,(([tblTracking].
[Received]) Is Not Null), IIF([forms].[fmQryByClinicSurveys].
[emailOptions]=2,(([tblTracking].[Received]) Is Null),Null)) OR
[forms].[fmQryByClinicSurveys].[emailOptions]=3

The [emailOptions] control is the name of the option group frame on
the form.  When I use Is Null or Is Not Null alone under the date
field, I get displays (1) and (2).  I just don't know how to translate
this into my SQL statement.

Thanks for your help, I thought I understood what I was doing.
Dara

Thank you so much for the detailed explanation, it's so helpful. I
learn so much from you MVPs on this in this forum! Would you have any
recommendations for a good book to teach SQL to a beginner with little
experience in program language?

Thanks again,
Dara

 

[Seif Hanbali]

"Hey whats up everyone im new here and i dont understand most of this can u
uguys give me a tips up thanks"KenSheridan via AccessMonster.com"

Dara:

You should be able do this by using OR operations on parenthesised AND
operations:

SELECT *
FROM tblTracking
WHERE
(Forms!fmQryByClinicSurveys!emailOptions = 1
AND Received IS NOT NULL)
OR
(Forms!fmQryByClinicSurveys!emailOptions = 2
AND Received IS NULL)
OR
Forms!fmQryByClinicSurveys!emailOptions = 3;

So:

1. If the value of the option group is 1 and the Received column is not
Null,
the WHERE clause will evaluate to TRUE and the row will be returned.
Or:
2. If the value of the option group is 2 and the Received column is Null,
the WHERE clause will evaluate to TRUE and the row will be returned.
Or:
3. If the value of the option group is 3, the WHERE clause will evaluate
to
TRUE regardless, i.e. all rows will be returned.

As well as handling alternative parameters like this you can also use
similar
logic to optionalize parameters, but in that case its AND operations on
parenthesised OR operations. This will generally be better that calling
the
IIF function as to extend the number of optional parameters its merely a
case of tacking on another parenthesised expression, whereas with an IIF
function you could soon end up with nested function calls of Byzantine
proportions to cope with all the permutations.

Ken Sheridan
Stafford, England

I recently posted a question about option groups passing info to a
query for a Yes/No field, and received some great advice. However,
I've tried to apply that advice in a similar way to a Date field, to
no avail. In the query, I want the user to be able to display (1) only
records with dates present or (2) only records without dates present
or (3) all records.

I changed the query criterion that worked for the checkbox as follows,
and placed it under the Date field which I want to select by (this
statement yields no records):

IIF([forms].[fmQryByClinicSurveys].[emailOptions]=1,(([tblTracking].
[Received]) Is Not Null), IIF([forms].[fmQryByClinicSurveys].
[emailOptions]=2,(([tblTracking].[Received]) Is Null),Null)) OR
[forms].[fmQryByClinicSurveys].[emailOptions]=3

The [emailOptions] control is the name of the option group frame on
the form. When I use Is Null or Is Not Null alone under the date
field, I get displays (1) and (2). I just don't know how to translate
this into my SQL statement.

Thanks for your help, I thought I understood what I was doing.
Dara

 

[rocketD]

Dara:

Take a look at 'SQL Queries for Mere Mortals' by John Viescas and Michael
Hernandez.  I don't have it myself, but from the excerpts I've seen it looks
to be comprehensive and authoritative.

Amazon are currently offering it at 17.95 GBP, which is a snip:

http://www.amazon.co.uk/SQL-Queries-Mere-Mortals-Hands/dp/0321444434/...

Amazon.com have it at 37.79 USD:

http://www.amazon.com/SQL-Queries-Mere-Mortals-Hands/dp/0321444434/re...

Ken Sheridan
Stafford, England

[quoted text clipped - 62 lines]

- Show quoted text -

Thank you so much for the detailed explanation, it's so helpful.  I
learn so much from you MVPs on this in this forum!  Would you have any
recommendations for a good book to teach SQL to a beginner with little
experience in program language?

Thanks again,
Dara

Thanks, Ken - I will certainly check it out.

Dara