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
590 Views

Similar Articles

[PageSpeed] 15

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:

Why No "Format Cells" Choice Option Always ?
Hello, Using Excel 2007. When I select a Column of numbers, I usually see a choice for "Format Cells" At other times, for different Columns (also with numbers in the cells, I do not get the Format Cells choice. All that shows up as being selectable are: Cut, Copy, Clear Contents Why might this be ? Thanks ...

RMS matrix attribute option
Adding an existing item in the RMS 2.0.0114 matrix with 219 attributes and 826 items and saving it takes about 2 hours to complete (using Windows XP SP2 Pentium D 3.0 Ghz with 2 G RAM). Since the backend is SQL Server, we don’t see any reason for the application not to support matrix records of this size, considering that the Item has 219 attributes and 826 items only. I think it would be better if RMS has the option not to load the existing attributes for the dropdown list. Just enable the addition of new dimensions. ---------------- This post is a suggestion for Microsoft, and Micro...

combining two groups into one
I upgraded to Outlook 2003 and pulled over the Inbox from "old" Outlook. Now, somehow, I have two groups in my OL 2003 Inbox - one that has all my old messages from Ols OL and a new one with any messages since the upgrade. Strangely, both groups indicate they use the same e-mail account - all the account settings are identical. But only the "newer" group actually receives any new messages. The "old" OL inbox group does not receive the new messages. If I try - out of despiration and confusion - to delete the "new" group, it will be deleted, but...

Why is redirect rule absent from my rules and alerts options?
Outlook 2003 help at <http://office.microsoft.com/en-au/outlook/HA011502011033.aspx?pid=CH063564671033> offers a choice of either forwarding or redirecting mail. I want to redirect mail to a webmail address to access it more easily while travelling. Redirect suits me better than forwarding, as forwarded mail all appears to come from me (i.e. original sender is not visible until mail is opened -- bit inconvenient). The forwarding option/s are visible among the checkboxes under 'Rules and alerts' but there is no sign of the 'Redirect it to people or distribution list&#...

Tools,Options, Email Options, AdvancedE-mail Options,
Hi, Windows XP, Outlook 2003, Desktop Alert stop popup, When it check in - Tools,Options, Email Options, AdvancedE-mail Options, It supposed to be "Preview" option, its not , In "When new item arrive in my Inbox" all the option mark, But when new mail arrived Desktop Alert not popup, What could be the problem Thank you & Bets Regards, Yakov Do you have a rule set to move messages? If you do then you need to add showing the Desktop Alert to that rule. "Yakov" <Yakov@discussions.microsoft.com> wrote in message news:304C2EEA-5839-47B1-A737-475921A51D...

Options okn Tools Menu
I have a user who is using Excel 97. When he goes to the menu bar and selects Tools then Options, the Options chioce is there but when you clck on it it does nothing. Has anyone had this issue before and if so how can I get his Options functionality back? Excel and Office has been uninstalled and reinstalled several times. Maybe this is it: XL97: Page Fault or Violation When You Click Options on the Tools Menu http://support.microsoft.com/default.aspx?scid=kb;en-us;108032 sounds like it describes your Tools|Options problem (it says it was corrected in xl97 SR2). Ron Davidson wrote:...

Power options issue 06-24-10
When I click on Control Panel and then Power Options and set turn off monitor to 20 minutes and turn off hard drive to 20 minutes nothing happens. However if I change those settings to turn off monitor to 1 minute and turn off hard drive to 3 minutes it works every time. I do not know what the problem is or how to correct it, so any help would be greatly appreciated. You should also know that no changes to my system have been made since this problem started happening. The turn off cannot be executed until the computer is not running anything for the amount of time you selec...

IMAP Status
In O2003, IMAP, Inbox, there is a default view called "Group Messages Marked for Deletion". The view groups the message marked and not marked for deletion by using this field (IMAP Status) in the Group By box. I tried to locate this "IMAP Status" field but cannot locate it. I looked through all the available fields but cannot locate it. Does anyone know where it is located? Shawn ...

Change printing options based on tender types
I have curerntly setup our POS registers to automotically print 40-column receipts and to prompt the user for whether or not to print a large 80-column invoice. However, I would like to have large invoices automatically print for all charge account tenders. Is there a way to specify the print behavior based on the tender type for the transaction? Thanks, Paul Arenson Anderson Plywood Sales, Culver City, CA not without an add-in. Seems like someone posted something about that within the last few weeks - try searching the newsgroup. -- Glenn Adams Tiber Creek Consulting http://ww...

Exchange Disaster recovery Group
Hi, Have successfully been to restore a mailbox using the Exchange Disaster Recovery Group. Have dismounted the database, can I now delete the database from the RSG and all the associated files from the folder on the drive? Thanks, Abhi Yes. If you are done with the Recovery Storage Group, simply dismount the database, then delete the database and Recovery Storage Group from System Manager, and delete the associated files on your drive. -- Ben Winzenz Exchange MVP MessageOne Read my blog! http://winzenz.blogspot.com http://feeds.feedburner.com/winzenz (RSS Feed) "Abhi" &...

SQL Query and connection help
I would like to have a excel sheet connect to a db and run a query that can be refreshed. Can someone give me an example on how to do this with vb in code view? I found many examples but either none work, or I can get the data to be displayed on the worksheet. Excel 2007 ...

Remove Marketing and Service Option from Left Nav Bar
When the only entity selected in a security role is Account access, we would like the options for Marketing and Service not to be seen in the left hand Nav bar. -- Thanks, Justin ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutio...

AP Trail Balance should have option to select by due date
I believe the AP Aged Trial Balance could be enhanced to allow to select transactions by a due date range. That way the report will provide a list of invoices that a due within a specific due date range. I have a clinet that has requested this functionality. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click ...

Adding on Option to an Option Group
I have created a form to give a report of 3 tables and give the user the ability to sort on a column by checking the check box on the header. I had to add another field to one of the tables and now need to add this to the option group.. how? Thanks Sue Found it - for others, you need to add the field to the record source for the form, then with the option group selected, open the field list and drag the new field onto the option group (at least I hope that is it) Sue (Sometimes it actually pays off to have a stack of books over a foot high on Access) "Design by Sue" wrote...

Insert options
I've recently been "upgraded" to Office 2010 from Office 2003 and find the "Ribbon" interface really horrible. A good example is in Excel. In Excel 2003 if I wanted to insert a row or column it was simple. Go to the insert menu and select either rows or columns. To do the same task in Excel 2010 the logical place to go is the "Insert" ribbon. This lets me insert lots of things, but none of them are rows or columns. To do that I have to go to the "Home" ribbon and then there is an Insert button from where I can insert rows or columns. I thought one ...

How to revalue an option buttons
Hi, I have option group in from to add values in table. These options use to add a percentage to the table. The first one has 50% labels; the second one has 55% and so on to 100%. The problem is when I retype the value of each option from 1 to 0.50 the value returns to 0. Can any body help me revaluing the value of each option? Thanks!! Check the field type, if its Long or Integer it will round the number and 0.5 will turn into 0. Change the field type to Double or Single -- Good Luck BS"D "Jon" wrote: > Hi, > I have option group in from to add values in table...

exporting xml from access 2000 query
Hi Im trying to convert an access 2000 query to xml with this vba code Attribute value VB_Name = "XML" Option Compare Database Option Explicit Sub QFN(MyQName As String, WithFormats As Byte) Dim MyDb As Database, TDefLoop As QueryDef, MySet As Recordset, MyFormat() As String Dim FNames() As String, n As Integer, MyF As Field, Tt As String, NumF As Integer Set MyDb = CurrentDb() For Each TDefLoop In MyDb.QueryDefs If TDefLoop.Name = MyQName Then Debug.Print "List of fields in '" & UCase(TDefLoop.Name) & "'" NumF = TDef...

data validation
Is it possible to create a similar function like data validation, from which I can select multiple options? (Background: I need a list of many names, from which I case by case can select). Not really. Maybe you could use a listbox. I added a listbox from the Forms toolbar to a worksheet. I called it "List Box 1". I rightclicked on that listbox and chose Format Control. On the Control tab, I chose Selection Type of Multi. I also pointed at the range that held the values that go in that listbox. Then I added a button from the Forms toolbar (I click the button when I'm d...

Validation Options with option List
Good morning! i have set up an validation list option in Excel so i can choose what option to pick. For example, i can choose test1, test2 and/or test3. But what i want to try to do is when i choose one of the options test1,test2 and/or test3 i want for those options different fields like for example: Test1 this is the text of data for test1 When i choose the option Test2 i want the text or data cleared for test1 and see the text for option 2. I hope you can help me with this problem, thanks in advanced! Edo -- EdoZwart ---------------------------------------------------------------...

prevent ad-hoc queries?
I think I already know the answer to this question, but is there a way to prevent users from issuing ad-hoc queries via Query Analyzer, Management Studio or some other query tool? I want their access to data to come only from my program. Thanks. You don't say what type of application you are coding but one possibility would be to use an Application role, see: http://articles.techrepublic.com.com/5100-10878_11-5068954.html?tag=rbxccnbtr1 -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent...

Problems with Excel Horizontal arrays with regional options using.
Entering a vertical array ={10;20} works ok, but horizontal array ={10,20} gives 10,2 ;-) in both cells instead of 10 and 20 in the two cells. This happens, I presume, because I work with a comma as decimal separator and NOT with the American . I do suppose that Excel has got some solution for this behaviour. I, in principle, do NOT want to change the regional options. ...

Program Name Options
I cannot find the Program Name Options when I select Microsoft Office Button. It is not there. How do I get it? Look in the lower right corner of the block, just inboard of the Exit "X" Richard LeBlanc wrote: > I cannot find the Program Name Options when I select Microsoft Office > Button. It is not there. How do I get it? Thanks "Bob I" <birelan@yahoo.com> wrote in message news:u7UaEj6vKHA.5940@TK2MSFTNGP02.phx.gbl... > Look in the lower right corner of the block, just inboard of the Exit "X" > > Richard LeBlanc wrote...

Help with a query 02-18-10
How do I get my query to return all of the text to the right of the ~ character. For instance for the following string "Terms Template ~ Bob's Communications" I just want Bob's Communications to appear. Thanks, Terry Carroll Can you guarantee there always will be a '~' character? You can use the INSTR() function to find the position of the squiggle, then the MID() function to take the portion of the string from that position. I think this will do it (wont work if squiggle is missing). MID(MyString,INSTR(MyString,'~')+1) Look these funct...

Second Calendar Option?
Outlook 2000. This is probably a very easy question (I hope). How do I setup a second apptoinment calendar so I can keep my personal appointments seperate from business ones? -- Thanks in advance... Bob File | New | Folder. But if you want to get reminders from the second calendar, you'll need the tool from http://www.slovaktech.com . A better approach might be to use different categories for personal and business appointments and a filtered view. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developer...

Can't mount my mailbox store in my first storage group for exchang
Here is something I'm seeing a lot in my event viewer: Product: Exchange Event ID: 9175 Source: MSExchangeSA Version: 6.5.0000.0 Message: The MAPI call 'value' failed with the following error: error code That even ID 9175 is all over my event viewer - probably about 20 instances of it right after I reboot the comput. I think I've narrowed down the problem to - I can't mount my Mailbox Store in my First Storage Group for Exchange. I also get this in my event viewer: Product: Exchange Event ID: 455 Source: ESE Version: 6.5.0000.0 Message: process name (proc...