Options Group and Query Criteria, revisited

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
0
rocketD
12/9/2009 9:47:42 PM
access 16762 articles. 3 followers. Follow

5 Replies
719 Views

Similar Articles

[PageSpeed] 11

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

rocketD wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

0
KenSheridan
12/9/2009 10:48:06 PM
On Dec 9, 3:48=A0pm, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:
> Dara:
>
> You should be able do this by using OR operations on parenthesised AND
> operations:
>
> SELECT *
> FROM tblTracking
> WHERE
> (Forms!fmQryByClinicSurveys!emailOptions =3D 1
> AND Received IS NOT NULL)
> OR
> (Forms!fmQryByClinicSurveys!emailOptions =3D 2
> AND Received IS NULL)
> OR
> Forms!fmQryByClinicSurveys!emailOptions =3D 3;
>
> So:
>
> 1. =A0If the value of the option group is 1 and the Received column is no=
t Null,
> the WHERE clause will evaluate to TRUE and the row will be returned.
> Or:
> 2. =A0If the value of the option group is 2 and the Received column is Nu=
ll,
> the WHERE clause will evaluate to TRUE and the row will be returned.
> Or:
> 3. =A0If the value of the option group is 3, the WHERE clause will evalua=
te to
> TRUE regardless, i.e. all rows will be returned.
>
> As well as handling alternative parameters like this you can also use sim=
ilar
> logic to optionalize parameters, but in that case its AND operations on
> parenthesised OR operations. =A0This will generally be better that callin=
g the
> IIF function as to extend the number of optional parameters =A0its 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
>
>
>
>
>
> rocketD wrote:
> >I recently posted a question about option groups passing info to a
> >query for a Yes/No field, and received some great advice. =A0However,
> >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]=3D1,(([tblTracking].
> >[Received]) Is Not Null), IIF([forms].[fmQryByClinicSurveys].
> >[emailOptions]=3D2,(([tblTracking].[Received]) Is Null),Null)) OR
> >[forms].[fmQryByClinicSurveys].[emailOptions]=3D3
>
> >The [emailOptions] control is the name of the option group frame on
> >the form. =A0When I use Is Null or Is Not Null alone under the date
> >field, I get displays (1) and (2). =A0I 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
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Foru=
ms.aspx/access/200912/1- Hide quoted text -
>
> - 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
0
rocketD
12/10/2009 3:13:08 PM
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/ref=sr_1_1?ie=UTF8&s=books&qid=1260461204&sr=8-1


Amazon.com have it at 37.79 USD:

http://www.amazon.com/SQL-Queries-Mere-Mortals-Hands/dp/0321444434/ref=sr_1_2?ie=UTF8&s=books&qid=1260461928&sr=8-2


Ken Sheridan
Stafford, England

rocketD wrote:
>On Dec 9, 3:48 pm, "KenSheridan via AccessMonster.com" <u51882@uwe>
>wrote:
>> Dara:
>>
>[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

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
12/10/2009 4:28:33 PM
"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" 
<u51882@uwe> wrote in message news:a059bdfc8cc86@uwe...
> 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
>
> rocketD wrote:
>>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
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1
> 

0
Seif
12/10/2009 11:59:15 PM
On Dec 10, 9:28=A0am, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:
> Dara:
>
> Take a look at 'SQL Queries for Mere Mortals' by John Viescas and Michael
> Hernandez. =A0I 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
>
>
>
>
>
> rocketD wrote:
> >On Dec 9, 3:48 pm, "KenSheridan via AccessMonster.com" <u51882@uwe>
> >wrote:
> >> Dara:
>
> >[quoted text clipped - 62 lines]
>
> >> - Show quoted text -
>
> >Thank you so much for the detailed explanation, it's so helpful. =A0I
> >learn so much from you MVPs on this in this forum! =A0Would you have any
> >recommendations for a good book to teach SQL to a beginner with little
> >experience in program language?
>
> >Thanks again,
> >Dara
>
> --
> Message posted viahttp://www.accessmonster.com- Hide quoted text -
>
> - Show quoted text -

Thanks, Ken - I will certainly check it out.

Dara
0
rocketD
12/11/2009 7:10:47 PM
Reply:

Similar Artilces:

Options missing from the menu bar
We have an access2003 database that records calls logged. Now we have an issue where the person who created the database left the company and we need to make a change to the access database. When he created the database he took out the options to make changes by suppressing the menu bar. We are missing the drop down menus for "Tools" and "View". How can we get these option back onto the top menu bar as we need to make changes. Any help is appreciated-- Ron P...

mailto option
Hi... I am new to this so I hope someone can help. I have a list of ID's (020202, for example) in column A and text in column B (@blah.blah.blah) together they make up an email address. I was hoping to just CONCATENATE them into column C to have a link which I could click, which in turn would fill in the To: box in an email message. However, this does not work. If I type the email address directly into the cell it works but as I have about 1000 of these ID's I was hoping I didn't have to type each email address manually. Any ideas?... Anyone?... Please?... Your help is app...

lookup type metadata lost when usining Send To option
When I move a document between folders (even in the same library) any lookup type metadata gets lost. I have already reported this problem in a another post, a couple of weeks ago, but still have no solutions yet. I have tested the Send To option on three different WSS 3.0 and MOSS 2007 farms and I get the same result. I can't find any post reporting such issue. Has anyone succeeded in using Sharepoint' Send To option to move documents with custom content type, having lookup type metadata, whitout losing metadata values? When moving items inside of SharePo...

Query Help 12-07-09
I need help on what I think should be easy query but I'm not getting it. I have two tables Parts, and Assemblies Parts has the fields as follows ID - Long PartCode - Char :If it is a P it is a part if it is an A it is an Assembly Active - Boolean Description - Text Assemblies has the fields as follows ID - Long Assembly ID - Long : this is the ID of the Assembly Part ID - Long : This is the ID of the parts and/or Assemblies used to make up the assembly What I want to do is have a query that will show me all active assembly parts that are not used in another active a...

This group has cooled down
Interesting how this group has cooled down. Nothing seems resolved so the cycle just starts over again for 06. This group has cooled down. I guess they just got tired of M05 not working and not getting any help from MS. Maybe they switched to Quicken. "someone" wrote: > Interesting how this group has cooled down. Nothing seems resolved so the > cycle just starts over again for 06. > > > Flyfishing Fool wrote: > This group has cooled down. I guess they just got tired of M05 not working > and not getting any help from MS. Maybe they switched to...

add fields to query in closed data base
thank you Can I change (record source ) for field in report in (close) data base by code using current (open ) data base. Field1 record source Or field source = Playername Change to Field1 record source Or field source =PlayerN --- Is possible to add field to query in closed data base by code using current open database notes query is created in close database the query contain fields I want add field to this query ...

Exchange 5.5 Attribute Query
Hi, I’m in the preparation stage of Migrating Multiple Exchange 5.5 Site to Single Exchange 2003 Site. I would like to query the Account’s Alternate Recipient(under delivery option) and Forwarder Field in Ex55. Is there any tool that I can use to perform such query? You want to use Header.exe and then export pointing to that file. The tool can export every attribute of the 5.5 directory. See: http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.exchange.admin&mid=844ef2b2-8020-4265-a8a3-1ce73ae8722d&sloc=en-us "pte&quo...

get a list of sequence number that meet 3 criteria 12-07-09
How do you get a list of sequence numbers of 5 that meet 3 criteria? There are 1-59. I want to get a list of sequence numbers that consists of 5 number being called. Below are 3 criteria which consist of 24 to 28 group and each group has 1-3 numbers. Only one number is call from each group and no group will have two number call in the same sequence. There should be 5 number called. For example: 16 35 37 42 51 criteria 1 criteria 2 criteria 3 group 1 59 group 1 50 group 1 50 group 2 58 group 2 40 group 2 40 51 group 3 49 group 3 51 group 3 30 41 52 gr...

Everyone group is missing
I have no "Everyone" distribution group. I can't create one as it says the object already exists. I cannot resolve in outlook. Help? On Tue, 6 Jun 2006 10:34:02 -0700, John L <JohnL@discussions.microsoft.com> wrote: >I have no "Everyone" distribution group. I can't create one as it says the >object already exists. I cannot resolve in outlook. Help? huh? The everyone group is a builtin group controlled by OS. If you add permissions to an object you should be able to resolve it. If you want an All Users DL for the GAL, create it, call it wha...

Conversion of MS Access queries to SQL queries
Hi All, Anyone knows of a utility or software that will convert MS Access queries to SQL Queries. I need to convert my existing ms access queries to SQL queries as i need to use them in a software which only takes SQL queries. I tried to copy and paste "SQLVIEW" of the queries from MSaccess but they dont' work as they require modifications. I don't have much knowledge of SQL queries, wondering if someone can point me in right direction. Thx In article <1181230883.726006.250120@q66g2000hsg.googlegroups.com>, raonhassan@yahoo.com says... > Hi All, > Anyone knows...

Dictionaries for grouping
Hi, How to make groups from attached dictionary. I cant modify source data, grouping from dictionary would make my work lot easier. See if this helps: http://tinyurl.com/6uc2p -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Piotr" <hokah@wp.pl> wrote in message news:af2bdc1c.0505192349.6a0f928f@posting.google.com... Hi, How to make groups from attached dictionary. I cant modify source data, grouping from dictionary would make my work lot easier. ...

need help with query
Dear Guru, I need help with sql query that combine two tables, and filter. I have following tables: tblInbox, tblInboxThread, tblInbox ID, UserFrom, UserTo, vSubject, vMessage, dCreateDate tblInboxThread ID, iInboxThread, ThreadUserFrom, ThreadUserTo, vThreadMessage, dCreateDate where the iInboxThread is referring to ID of tblInbox =============================================== I would like to extract the most recent message (by max creation date) to be displayed in inbox for each inbox ID Example of table contents: tblInbox ID UserFrom UserTo...

Monthly transactions in query
Please help me in creating Query form below records: RecDate Details Debit Credit 01-Jan-07 Sales 2 03-Jan-07 Sales 3 10-Jan-07 Payment 5 12-Feb-07 Sales 9 15-Feb-07 Sales 10 with query Like this: RecDate Details Debit Credit 01-Jan-07 Sales 2 03-Jan-07 Sales 3 10-Jan-07 Payment 5 31-Jan-07 Month Total 01-Feb-07 Balance B/F 12-Feb-07 Sales 9 15-Feb-07 Sales 10 28-Feb-07 Month Total 01-Mar-07 Balance B/F in my table i have transactions for all months; i want to make month total and for new month i want to carry the balance as the beginning date of the month. All yo...

Pop3 Option Disabled in Add New Email Account Diag Box
Installed Outlook 2003 on WinXP Pro SP 2 and noticed that the only email server option available for the creation of new email accounts is "Exchange Server." I am unable to add a Pop account because the option is grayed out / disabled. Why is this and what can I do to reenable this option? Thanks! HKEY_CURRENT_USER/Office11/Outloook/DisablePOP3 is set to 1. It needs to be set to 0. This was a decision that the admins who installed it probably made, but in my case I need the POP access. Thought I would share. If you don't have admin privileges on your computer to be edi...

Report Options Corruption? IV70500 SY70500
Upgrading to GP 10 from GP 8 SP5 We were doing some testing of the reports and it appears that some corruption has\(had) occured. When selecting any of the historical stock status reports in the report range it says "From start to ÿÿÿÿÿÿÿÿÿÿÿ" Erasing this range, saving the report, getting back into the report the same range appears with the range shown above. Looking at the iv70500 table quite a few of the columns are showing the YYYY characters. Trying to recreate a brand new inventory report from scratch it will stil...

Outlk 02 group send problem
When I want to send an email to a group it acts as if it's sending but nobody receives the email. Whether I select one group or select multiple individuals the email does not send. It sounds like you have two problems. Sending to a group and sending to more than one person. How do you insert the names on the TO: line? Easiest way is to click on the TO: and then highlight each user / group and then dont forget to click on the TO: / CC / BCC button to move highlighted name to the reciepient box. Thom >-----Original Message----- >It sounds like you have two problems. Send...

Loop through Query Names?
Hi, I'm trying to code a combo box and want to define a String variable and have a For..Each..Next loop that goes through all the Queries in my database (actually an .adp Access project) and add each query name to the string. Then I can make my Combo's Row Source the string (after concatenating with char(34) and ";"). This will enable the Combo to always show all the queries in the database. However, when I try and set my For Next statement there isn't a 'AllQueries' option for me to add to the end of the following statement: For Each AccessObject In...

Update Supplier ID By Deparment ID SQL Query
Hello, I am trying to figure out an SQL query to update supplier ID. I purchase all items from 1 department from only 1 supplier. So I need to update all items in Department X to Supplyer Y. -Michael Santore Test on a test system before doing the following on your live system. Always backup your live system before performing any updates. Perform the following SQL queries in Store Operations Administrator ====================================================================== Step 1. Find the DepartmentID by running this query. SELECT * FROM Department Step 2. Find the SupplierID by ...

how to NOT show a listbox in a query / report that is defined at the table level?
I have a listbox defined in the lookup tab of a table. I like it that way because I don't have to mess around with forms - I'm the only one using this database, and I find it easier just to enter data into the table. But when I go to do a report, that listbox carries over. I tried creating a query, hoping the listbox would disappear - nope its there. I want to tell Access to just query the data - snapshot (which I selected) - not show me a listbox for data entry. And the same goes for the report. I only want that listbox to appear when I'm in the table. Any ideas? I'm us...

remove exchange 5.5 server from admin group
When using System Manager (Exchange 2003) I see 2 admin groups. 1 group contains our 2003 server, the other group contains a 5.5 server that is no longer online. I need to remove this groups somehow so I can switch to native mode. I have deleted everything from the Site Replicator, but this group will not go away. Ive read that you need to use the Exchange Administrator program to connect to the 2003 server, which I did. But they want you to delete the 5.5 server from here, which I can't even see because it is in the other admin group. Can someone point me in the right direction? ...

Multiple IF THEN criteria...
=IF(B20="r","-2",IF(B20="5","-1",IF(B20="=","1",""))) The "5" displays blank, all the others work... any reason why? I am trying to be get so if in B20 someone enter's r "-2" diplays; 5 "-1" diplays; = "-2" diplays; and if they enter nothing then nothing displays. thank you! "Jason" wrote: > =IF(B20="r","-2",IF(B20="5","-1",IF(B20="=","1",""))) > The "5" displays blank, ...

add values from four columns in query ms access 2003
hello, I want to add (som) values from four different columns to have a total score from four columns. My columns are person, kwartaal 1, kwartaal 2, kwartaal 3, kwartaal 4. I want the total score from kwartaal 1,2,3,4 together of each person. Somehow I can't manage it. Can you help me ? Create a field in your query like this: TotalScore: Nz(kwartaal 1,0) + Nz(kwartaal 2,0) + Nz(kwartaal 3,0) + Nz(kwartaal 4,0) The Nz() function will take care of any that may be Null. Steve -- Steve McGuire MCSD, MCAD, MCP "Jeroen" wrote: > hello, I want to add (som) values from fo...

proper filter syntax for query-based DL
I want to have a query-based DL for people that are members of a certain security group. What is the proper syntax for the query? do I need to use the distinguished name? For example, I tried a custom filter of: Field>User>Member of>is (exactly)>cn=group name,OU=Security Groups,DC=domain,DC=com and it didn't work. does it need quotes? Can someone point me down the right path? I have had success with querying other AD attributtes like Office Location. Thanks, Rich Does not require quotes. In QBDL properties | query | modify | Exchange tab -> select "Users with ...

If criteria not met use msgbox
I am very new to code. I have created macros in the past. How can I write code that would let me manipulate data. For example: I want them to enter a date that automatically puts Sunday in the cel above it (Cell C5). I have this done in a formula so far!! What I want if it's not Sunday, is a message box that pops up and say "the date you entered is not equal to Sunday, change the date. " The go back to the cell so they can enter the new date? I am sure this is basic for some. I just can't get it to work. Michael; -- Message posted from http://www.ExcelForum.com Is this...

Web Queries #2
Hello, I would like to write a macro to do some automated web queries for me. I've got one so far that will build the URL of the page I want; now I would like to query each page and put it in a new worksheet. How should I go about doing this? Thanks, Andrew ...