Return All Record if Check Box is Blank

How would I return a subset of records if a form check box is checked but 
return all records if the check box is blank.

The formcheck box is: [Forms]![frm_switchboard]![chk_rndm]
The field [RandomMarker] can have the values of "Y" or Null only.

The following SQL pulls the correct data when the check box is checked, but 
I recive an "OpenForm action was canceled" when the box is blank. How would I 
modify the query to pull all results ([RandomMarker]="Y" or Null) when the 
box is not checked?

SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, 
tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo
FROM tbl_tt_trans
WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND 
((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND 
((tbl_tt_trans.PtType) Not Like "O"))
ORDER BY tbl_tt_trans.UseDT;

thanks for the help

0
Utf
12/1/2009 4:29:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
894 Views

Similar Articles

[PageSpeed] 11

SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE,
tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo
FROM tbl_tt_trans

WHERE tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]
AND
  (tbl_tt_trans.RandomMarker=IIF([Forms]![frm_switchboard]![chk_rndm],"Y",Null)
   OR [Forms]![frm_switchboard]![chk_rndm] = False)
AND tbl_tt_trans.PtType) <> "O"

ORDER BY tbl_tt_trans.UseDT;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

yator wrote:
> How would I return a subset of records if a form check box is checked but 
> return all records if the check box is blank.
> 
> The formcheck box is: [Forms]![frm_switchboard]![chk_rndm]
> The field [RandomMarker] can have the values of "Y" or Null only.
> 
> The following SQL pulls the correct data when the check box is checked, but 
> I recive an "OpenForm action was canceled" when the box is blank. How would I 
> modify the query to pull all results ([RandomMarker]="Y" or Null) when the 
> box is not checked?
> 
> SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, 
> tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo
> FROM tbl_tt_trans
> WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND 
> ((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND 
> ((tbl_tt_trans.PtType) Not Like "O"))
> ORDER BY tbl_tt_trans.UseDT;
> 
> thanks for the help
> 
0
John
12/1/2009 5:24:19 PM
Thanks John. works great!

"John Spencer" wrote:

> SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE,
> tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo
> FROM tbl_tt_trans
> 
> WHERE tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]
> AND
>   (tbl_tt_trans.RandomMarker=IIF([Forms]![frm_switchboard]![chk_rndm],"Y",Null)
>    OR [Forms]![frm_switchboard]![chk_rndm] = False)
> AND tbl_tt_trans.PtType) <> "O"
> 
> ORDER BY tbl_tt_trans.UseDT;
> 
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> yator wrote:
> > How would I return a subset of records if a form check box is checked but 
> > return all records if the check box is blank.
> > 
> > The formcheck box is: [Forms]![frm_switchboard]![chk_rndm]
> > The field [RandomMarker] can have the values of "Y" or Null only.
> > 
> > The following SQL pulls the correct data when the check box is checked, but 
> > I recive an "OpenForm action was canceled" when the box is blank. How would I 
> > modify the query to pull all results ([RandomMarker]="Y" or Null) when the 
> > box is not checked?
> > 
> > SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, 
> > tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo
> > FROM tbl_tt_trans
> > WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND 
> > ((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND 
> > ((tbl_tt_trans.PtType) Not Like "O"))
> > ORDER BY tbl_tt_trans.UseDT;
> > 
> > thanks for the help
> > 
> .
> 
0
Utf
12/1/2009 10:06:15 PM
Reply:

Similar Artilces:

Need message box to be at front of screen
Greetings all. I have set a timer on a form which has a message box that pops up at defined intervals telling the user they are still being timed. The problem is that if they are using another application with access minimized, the message box is not visible until they minimize all other applications that are on top of it. Is there a way to make the message box show on top of all other open applications? I am using Access 2003 with XP Pro. Thank you. -- Message posted via http://www.accessmonster.com If you are using the Access application to pop up an Access messagebox, I ...

Insert Check Box
Can anyone tell me how to insert a simple check box in an Excel worksheet. I have the actual check box on my worksheet, but when I click it, I want a checkmark to appear...and that is not what it is doing. Please help. Krista, Then what DOES it do when you click it? Does it select the check box (put handles around it)? -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Krista Cook" <ekcook@comcast.net> wrote in message news:3E293822-F641-4DD5-844A-91459560293F@microsoft.com... > Can anyone tell me how to insert ...

Displaying Message from event sink to Edit control in dialog box
Hai I am working on FTP Software using MFC and i have to write the ftp server request and response to tracer window. The server request and response were caught by Eventsink and i donot know how to pass this text to edit control in dialog box which is another class. Here is coding of Event Sink / EventSink.cpp : implementation file // #include "stdafx.h" #include "RoboFTPClient.h" #include "RoboFTPClientDlg.h" #include "Eventsink.h" #ifdef _DEBUG #define new DEBUG_NEW #undef THIS_FILE static char THIS_FILE[] = __FILE__; #endif extern long gnResult...

How do I ignore blank cells while averaging the solutions of equat
I'm trying to average (items produced/manhours/workers) over the course of a week. The problem is each job isnt worked every day, so I have blank cells in my equation. Here is what I'm typing: =AVERAGE((B4/C4/D4),(E4/F4/G4),(H4/I4/J4),(K4/L4/M4),(N4/O4/P4)). I know there is a way to ignore #DIV/0 results, but I'm fairly new to Excel. I'm also trying to keep this all in one row to save space. Any help will be appreciated. -Ryan take a look at the subtotal function and see if it meets your needs. -- Gary Keramidas Excel 2003 "Kis...

check Text
Dear Gentlemen, I have typed a text in a cell. Now want to have formula which checks that the required text is in the cell or not. For example cell A1 contains rth In Cell B1 I want the formula =IF(A1=rth)..... But whenever I type the above type of formula an error message occours Please give suggessions. Thank you G.Karthi -- fitzol ------------------------------------------------------------------------ fitzol's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33328 View this thread: http://www.excelforum.com/showthread.php?threadid=531506 =if(a1=&quo...

Outlook HTTP access generates blank message
Server: Windows & Exchange 2000 Client: Outlook 2003 We have some users that remotely access their email via the HTTP functionality in Outlook. Doing so is generating completely blank messages in their inbox � no subject, sender or recipient and the headers are totally blank. It seems like these messages usually appear after deleting a normal message from the Inbox. Any ideas what is causing this? TIA! Tony ...

failed to send item dialog box when sending(why)
I keep getting failed to send item when I hit send on outlook ...

Subform..Deleting a Record by code
I have a Before Update event to Trap an Entry of "SN" (which is not acceptable) and results in a Msgbox and "Cancel = True". If the User backspaces the "SN" leaving a Null, and leaves the Record, how can I delete that record by code? TIA - Bob "Bob Barnes" <BobBarnes@discussions.microsoft.com> wrote in message news:46A5C7AA-F19F-477E-938A-D99EE9CBF4DC@microsoft.com... > I have a Before Update event to Trap an Entry of "SN" (which is not > acceptable) and results in a Msgbox and "Cancel = True". > > If the U...

Criteria to return all records if selection from form is null
Hi, I'm trying to pass multiple query criteria from form controls. I'm using the following type of syntax for the criteria: Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*") And this works fine for the fields where there are no Nulls in the data, but if there are Nulls in this field I don't get those records. Quite understandable since Like "*" doesn't return Nulls. What I'm trying to do is to return all records when there is nothing chosen in the selection box on the form. I've tried a bunch of different IIfs wi...

Text box validation 01-04-08
I have a form with several text boxes that I want to check for duplicate values in the table. The PK inthe table is an Autonumber. What I want to do is not allow the FirstName LastName FileNo and SSN to be the same for any two records. The combination of these fields would garuntee that the same individual would not be allowed to have two records created. You could set a unique index in the underlying table on those four fields. This would disallow the entry of duplicates (but only after the fact -- after the user entered values and tried to save them). Another approach would be to g...

Record Notes Report
Is there a Record Notes report? We'd like to print out all the notes attached to customer records. You could do this by creating a custom report in Report Writer or using a SQL query. Join the SY03900(Record Notes master) to the RM00101(Customer Master) on the NOTEINDX field. Alternatively, you could pull this into a Crystal Report. If you use Report Writer you may have to first create the relationship between the tables. VGrinam "Elaine" wrote: > Is there a Record Notes report? We'd like to print out all the notes > attached to customer records. ...

Blank To and From Fields
We are on Exchange 2003 and my folks are receiving e-mail messages where both TO and FROM fields are blank. Is there a way to block messages when both TO and FROM are Blank? This is what the header looks like.... X-Comment: Sending client does not conform to RFC822 minimum requirements X-Comment: Date has been added by Maillennium Received: from X.X.X.X (unknown[X.X.X.X](misconfigured sender)) Thanks! -- NC Beach Bum And they are SPAM, right? Exchange 2003 allows you to filter messages that have a Blank sender. If you want to do this, you must enable it in 2 places. First, under Glo...

Excel 2007: Return rownumber when text data on 3 columns match exa
Using: Excel 2007 I want to document Windows Group Policy settings side by side on a single sheet. To accomplish this task I want to return the row number of the current sheet where the data of the row that has an exact match. The data I want to lookup is in three colums. Example: Column B | Column C | Column D ------------------------------------------------------------------------------------------- Policy | Control Panel | Load a specific visual style Policy | Desktop | Do not add shares Policy | Desktop | Hide Network Location...

Reprint AP check format on plain paper
Provide the ability to print a "copy" of a previously printed check w/ stub (with "void" in the signature section) ---------------- 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/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/defaul...

How do I insert a row of blank cells?
I need to know how to insert a row of blank cells every other row in the columns from F to I ONLY!!! I currently have just a straight set of data in those columns like data-data-data-data-data-data. I need to have it alternate data-blankrow-data-blankrow-data-blankrow- as I go down from row to row. I need to do this for about 1000 rows so I need a quick way to do it if there is one. HELP!!! Looks like this: Data Data Data Data I want this: Data Data Data Data Thanks. Chris -open the VB editor -double click the sheet of interest -View from the menu--> Code -Paste the belo...

Trim blank space within text
How can I remove the white space in text? eg. "12( 35)" into "12(35)" "12 3 5" into "1235" "123 (5)" into "1235" Possible to be done using TRIM function? Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Celia Try using the SUBSTITUTE function. Using this you can substitute all of the spaces with "" Andy. "celia" <celia.xe2vb@excelforum-nospam.com> wrote in message news:celia.x...

How do I resize a data label box in a pie chart to stop text wrapp
How do I resize a data label box in a pie chart to stop text wrapping? Excel decides how large to make these pseudo text boxes, based on the chart size, the amount of text, and the font properties. Aside from changing these, you have no control over the label sizes. You could replace the labels with textboxes, but these don't stay attached to a data point. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Eton Careers" <Eton Careers@discussions.microsoft.com> wrote in message news:B18C20DE-FD79...

Set focus in a specific record within a subform
I have a main form with a subform. The subform contains multiple records and a hidden unique id which ties it to the main form. After I enter data into a field on the subform, I am requering the main form. I would like the focus to remain on the subform on the specific record the user is editing. I have been able to get the focus to remain the subform however, the focus jumps to the first record. Any ideas? This is my code: Private Sub MP_Reserve_AfterUpdate() Forms!Liability.Form.Requery Forms!Liability!ClmntInfoForm!.SetFocus Forms!Liability!ClmntInfoForm!.Form![BI ...

Sync Services check boxes won't stay checked
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: imap Several computers in the office, running either 10.5.8 or 10.6.3, MS Office 2008, using Entourage sync services to sync with iCal. <br><br>Frequently, for no apparent reason, the top 2 sync services boxes come unchecked. Although we have on occasion run Onyx (like Cocktail), these events happen even when we haven't run Onyx in days or weeks. Sometimes it happens right after setting them, sometimes it works okay for a few hours or a day or two, but on these three computers, ev...

Is there a way to check
Is there a way to see what exhange is sending? I am having trouble sending outgoing mail to some domains. I think it is domains that do not have there own smpt site. If I do a nslookup -q=mx domainX, I get the correct smtp site for domainX, lets say MXdomain. I then do a telnet to MXdomain and am able to send a test message to xxx@domainX. BUT, if I just telnet to domainX telnet fails. DNS is working... Ready to pull my hair out - Steph http://www.msexchange.org/tutorials/Logging_the_SMTP_Service.html shows how to turn on SMTP logging.... post relevant excerpts and we will take a lo...

Excel
Dear Community, How can I change the size of the name box so I can see the whole name I gave to a specific range? Thanks, Marton Excel 2007 allows you do to this. In earlier versions, you cannot change the size of the box itself. You can, however, widen the width when it drops down. See http://www.cpearson.com/excel/NameBox.htm -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "VilMarci" <dontsend@here.mail> wrote in message news:uyhsjnDUHHA.1016@TK2MSFTNGP04.phx.gbl... > Dear ...

CRM4
Hi, I've used CRM 4 for about 2 weeks now and everything was working fine. The problem that i'm having now is that when I track an email in CRM and click on view in CRM it comes up with a blank page. this only happens on my machine, I checked pop ups, security, ran all updates to be on the safe side and still no resolution. any help is appreciated. url:http://www.ureader.com/gp/1265-1.aspx ...

Access 2007 form view search box
I have Office 2007 (moving up from Office 97) and am developing a database using Access 2007. In form view, at the bottom with the record selector is a search/find dialog box. It searches every field in the database however I want to limit it to searching only selected fields. Can I get at the code for this search function or do I have to turn off the navigation buttons and build my own record selector and search funtion? Thank you. -- JBM Jim, I don't think you can configure that search box in the horizontal scroll bar of A2007. Perhaps you could add something like this: ...

Subform causing duplicate records?
I am trying to do a Purchase Order database. I need a subform in the PO Data Entry Form since there are usually multiple items in the PO. I have a command button to Print PO but I end up with a report for each item in the subform. This is the SQL for the Print PO. SELECT DISTINCT [Purchase Order Table].[PO Number], [Purchase Order Table].[Purchase Order Date], [Purchase Order Table].[Requested By], [Purchase Order Table].[Approved By], [Purchase Items List Query].Quantity, [Purchase Items List Query].Description, [Purchase Items List Query].[Unit Price], [Purchase Items...

Text Box #2
I inserted a text box in excel using the click and drag text from the tool bar and entered the relevant comments in english and as soon as I finish the language changes to German. I have done all the basic stuff like clearing any formats, removing the text box and inserting a new box, checking langauge settings etc, but nothing works. I have than inserted 2 more text boxes in the same manner and typed the text in english and these have been fine. Can anyone tell what could be causing this problem? ...