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 with 
various use of quote marks, on the order of:

IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is Null 
Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])

but they either don't get all the records or they kick a "too complex" 
error.

I'm sure this must be easier than I'm making it.  Could anyone give me some 
help?

Thanks in advance,

Sue 

0
Sue
5/27/2010 3:44:18 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1804 Views

Similar Articles

[PageSpeed] 39

Sue Pari wrote:
>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 with 
>various use of quote marks, on the order of:
>
>IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is Null 
>Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])
>
>but they either don't get all the records or they kick a "too complex" 
>error.
>
>I'm sure this must be easier than I'm making it.  Could anyone give me some 
>help?
>
>Thanks in advance,
>
>Sue

I would probably build the filter on the fly (in the click event of a button)
and then pass the filter you build to a form/report when you open it.  There
are some examples of filtering reports by using listbox controls etc on
accessweb.  http://www.mvps.org/access/forms/frm0007.htm

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

0
PieterLinden
5/27/2010 4:08:09 AM
It is possible to craft the WHERE clause of the query to it evaluates to 
TRUE if the control on the form is null.

Switch the query to SQL View.
Locate the WHERE clause. It will be something like this:
    WHERE SomeField = [Forms]![ViewEdit Completed Procedures]![SelectOBy]
Change it like this:
    WHERE (([Forms]![ViewEdit Completed Procedures]![SelectOBy] Is Null)
    OR ([SomeField] Like [Forms]![ViewEdit Completed 
Procedures]![SelectOBy]))

Be careful with the bracketing when mixing ANDs and ORs.

This approach quickly gets unwieldy as you add more options.
It is much more efficient to build a filter string dynamically.
This article explains how to do that:
    Search form - Handle many optional criteria
at:
    http://allenbrowne.com/ser-62.html
Includes a downloadable sample file (free.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Sue Pari" <suepari@att.net> wrote in message 
news:uxysj7U$KHA.1700@TK2MSFTNGP02.phx.gbl...
> 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 with various use of quote marks, on the order of:
>
> IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is 
> Null Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])
>
> but they either don't get all the records or they kick a "too complex" 
> error.
>
> I'm sure this must be easier than I'm making it.  Could anyone give me 
> some help?
>
> Thanks in advance,
>
> Sue 

0
Allen
5/27/2010 9:15:35 AM
Sue,

One additional possibility if your table is fairly small OR if the field is 
not indexed.  Add a zero-length string to the field.  You can do this even if 
the field is indexed, it just means that the index won't be used and the query 
will take a bit longer to run.

Field: MySearchField: [Name of Field] & ""
Criteria: Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*")

You can experiment with this and see if the performance hit is acceptable.

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

Sue Pari wrote:
> 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 with various use of quote marks, on the order of:
> 
> IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is 
> Null Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])
> 
> but they either don't get all the records or they kick a "too complex" 
> error.
> 
> I'm sure this must be easier than I'm making it.  Could anyone give me 
> some help?
> 
> Thanks in advance,
> 
> Sue
0
John
5/27/2010 12:27:19 PM
Thanks to you all for the excellent advice!  I'll work through the different 
approaches and see what I can do.  I really appreciate the help.

Sue


"Sue Pari" <suepari@att.net> wrote in message 
news:uxysj7U$KHA.1700@TK2MSFTNGP02.phx.gbl...
> 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 with various use of quote marks, on the order of:
>
> IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is 
> Null Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])
>
> but they either don't get all the records or they kick a "too complex" 
> error.
>
> I'm sure this must be easier than I'm making it.  Could anyone give me 
> some help?
>
> Thanks in advance,
>
> Sue 

0
Sue
5/27/2010 7:51:43 PM
Reply:

Similar Artilces:

Code to fill Word form for multiple records from Access
This is the code I've been working with, but it freezes Access: Private Sub Command66_Click() 'Print Physician Profile. Dim appWord As Word.Application Dim doc As Word.Document 'Avoid error 429, when Word isn't open. On Error Resume Next Err.Clear 'Set appWord object variable to running instance of Word. Set appWord = GetObject(, "Word.Application") If Err.Number <> 0 Then 'If Word isn't open, create a new instance of Word. Set appWord = New Word.Application End If Set rs = Db.OpenRecordset rst.Open Me.RecordSource, CurrentProject.Co...

Record Review Process
Hi, Gang, I'm hoping you can help me out with a concept question. I have a database which collected employee information for several different group within our department organization. I need to institute a process in which each team lead needs to check off each of their employees as reviewed each month and be able to run a report to know which employee records have not been reviewed for the previous month. I have two tables already: one for the employees (tblFTE) and one for the review process (tblRev). I am using the EmpID as the parent-child field between the tables. Wh...

Input a date using a Form Button.
Hi, Please help as I'm totally lost with this one! I'm doing a little project for work and I have come across a littl problem with a date input that I have set up using a Form Button. No this button once pressed enters the date just as I want, into th correct Cell (the buttons called "Todays Date") Now I have recorded macro so that the button executes the =TODAY() comand - which is fin untill the date changes!! As you can emagine I need the date to stay a it was when first pressed as its a database I'm trying to compile. Does this make any sense to you all?? I will...

Omit points from plot if cell is formula returns a non-numeric res
How can I prevent cells with a non-numeric function result from being plotted as zero values. The function =IF(G3>0,F5+G3,"") Plots values as it should for G>0 but when G<=0 it plots points on the x-axis instead of omiting the points as it does when the cells are blank. On the spread sheet the cells display blank as they should. Hi, Charts plot all non numeric values as zero. There will however interpolate values if #N/A is used. =IF(G3>0,F5+G3,NA()) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Dave" <Dave@discuss...

Journal Form...?
On my computer at work, in the Outlook (Xp) Journal - form, the pull down for "Entry Type" is blank except for "Access, Excel, Power Point..." Where did the "Phone Call, Note, Fax, Meeting, etc..." go? How do I get the proper list back? I have tried [Help-Detect and Repair] I have tried Properties (in Form Design) but the first page of the Journal Form in design mode is null... The various Journal entry types are registry entries. See http://www.inquiry.com/techtips/exo_pro/10min/10min0999.asp for information about how to add custom entries. The same pr...

Converting varying length Vertically-aligned records to Horizontal Rows of list (tabular)
I have a Excel Spreadsheet with Records of books. The fields and data value is entered as the First and Second Column (ColA, ColB). The records are separated by 2 empty rows. Some fields are missing for some records: Some record may miss the 'author' whereas some records may miss some other fields, say ,'subject' AND 'totalpage'. The Objective is to tranform the record horizontally with Each ffield in one column with missing field blanked so that all the field are aligned in a column e.g. colA for 'Title', colB for 'Author' How can we convert varying ...

Create a button to find the next record
Hi I am trying to create a button to find the next record for a particular studentID. There is a main form and subform. I want to be able to find all relevent subforms for a particular student. The master link id is STUDENTID and the child link id is STUDENTID_FK. So for student #1, when I click on the subform and click on the button, it would cycle through all this student's records. What is the best way to set up this button? Thank you for your help in advance. You already have one that is built in. Just click the navigation button right of the record number ...

Run macro on selected sheets
Hi I have a workbook with 103 sheets, I want a macro to run on 100 of the sheets. Here is the macro, can anyone help me with this? Sub ReconsileStockCard() Range("D3").Select ActiveCell.FormulaR1C1 = "=SUM(R[4]C[-2]:R[97]C[-2])" Range("D3").Copy Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents Range("D7").ClearContents...

open form with no data
Hi, I have a form on which i present al lot of data from a database. On the form is a comobox which i use to select the right record en present the data of this record on the form. When i open de form, de combobox is empty (which is good !) but the rest of the form is already filled with the data of a record in the database (which is not good). When i choose a record for the combobox, the form is filled with the data of that particular record (which is good). So the only problem i have is on opening the form. Is it possible to open the form without it being filled with data...

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

use IUrlHistoryStg2 to get the history record,but how to get the user name ?
IUrlHistoryStg2 can tell you the url,titile,some visited time information.But how can I know who visit this url ? Anyone can give me some hint or code ? Thanks very much ...

Datagridview selected rows
I want to get the first selected row of DataGridView Selected Rows. The goal is that I would hold down CTRL or SHIFT and select multiple rows, then I could click a button or menuitem and update all selected rows with a value from the first selected row. For example in crystal reports you hold down shift and click multiple control and select Same Width and it resizes all to Width of the first one you clicked. Datagridview seems to works if you the select in order going down the grid, but from bottom to top its different. If I could find out the direction, then i could take the first ...

retrieving old records & saving them as new ones
hi all, I need to create a program in access database, the main form contains 3 subforms. for each month 'Monthly Progress Report for:', the user enters some details in the main form and the subs. these details should be retrieved (when the user tries to add a new record and enters a new 'Monthly Progress Report for:') and stored as new record with the new month stored also in the subforms. i hope i explained it well,.. your help is appreciated, Thanks. Data are stored in tables, not in forms or subforms. If you are talking about storing the month in the subform reco...

dlookup with multiple criteria
I have a form that needs to look up a "goal" by matching several fields in a table. I can't figure out how to do dlookup with multiple criteria frmManualTaskDataEntry [employee] [date] [mailcode] [state] [disabilityind] [volumecode] tblMailCodeTasks mailcode state disabilityind state goal Buzzmcduffie - You use AND to connect the multiple criteria, and must include proper delimeters for text and date fields. It will look something like this (untested): DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" &...

how can I attach a single access database form to an email
I want to attach a single database form to an email, im not sure if this is possible, Do you really mean a Form or a Report? Steve "tony" wrote: > I want to attach a single database form to an email, im not sure if this is > possible, Hi Steve, yes a report "SteveM" wrote: > Do you really mean a Form or a Report? > > Steve > > "tony" wrote: > > > I want to attach a single database form to an email, im not sure if this is > > possible, ...

Looping Through Records
I'm Using Sandra Daigles code to loop through and make a change to all records in a table. The field in the table "Select" is a Yes/No data type. It changes only the current record in my form. Here is the code. Any suggestion welcomed. Private Sub cmdDeSelect_Click() Dim rst As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb() 'Change this to your SQL or to the name of a saved query Set rst = db.OpenRecordset("Select * from tblCDMRvalues;") With rst ' Very important to avoid errors if no records are returned If Not (.EOF And .BOF) Then &...

Return of Yes/No Checkboxes
I set up some coding in my form in access to populate an unbound field automatically based on what values were entered in a series of checkboxes. This seems to work great most of the time, however, I noticed a few records for which it won't work. I started trying to debug it and noticed that for these records, the checkboxes are returning 0 for false and -1 for true. For the majority of my records in which this works, they are returning true or false. What would make these few records return different values? Thanks. Nick On Wed, 13 Jun 2007 15:06:05 -0000, Nick.Korynski@gmail.com wrote...

Date Difference Calculation Between two Records
If somebody can help me , regarding calculating difference of days between to records. Ex: Record No: Date: Receipts Payments 123 01/04/2007 10000 456 10/04/2007 5000 789 20/05/2007 500 What i need is calculate date difference in two records Record No: Diff 456 9 Days 789 40 Days I need this days for interest calculation if somebody could please suggest how to calculate this date difference in query i will be very much grateful ...

password protected to sub form
i have a form which don't want to be used by everyone.it should be protected with a password. i have Main form which calls many sub forms, one of the sub forms need to be protected with password. Below are the forms created in my database frmForm frmMain1 frmPassword frmForm has a command button(cmdDo) which calls Form(frmPassword). in frmPassword there is text box(PASSWORD),when i type password as A to my text box this should open my frmMain1 ...

Open form to specific record from button on continuous form
I have a continous form with limited data and I would like to have a button which then opens up a selected record in another form. I've placed the button via the wizard and, while it opens the form, it doesn't open to the selected record. It it possible to have a button on a continous form which would then open to a bookmarked record on another form? The wizard put this code, is it possible to modify this? Thanks. Private Sub btnOpenForm_Click() On Error GoTo btnOpenForm_Click_Err DoCmd.OpenForm "frmName", acNormal, "", """...

Xrefs from Form Fields not Visible
I've got a form that uses xrefs (bookmarks) to repeat field text elsewhere in the doc. Some of the xrefs are showing up as gray boxes, but not all are. The xrefs are functional even if invisible. Removing the \h switch from the xref fields and updating the field does not cause the field to become gray. The paragraph style is the same for both visible and invisible xrefs. I'd like them all to be shown as gray boxes so that my (tech-unsavvy) users can see that the fields are actually there. I do have Show Bookmarks (and Field Shading) checked ON in Word Options. Tha...

Creating a form from two tables
Im looking for assistance. This database is used for evaluations. so a manager completes a form that has 25 questions. They do 4 evaluations a month on each employee. I have 3 tables the Evaluation information (Table 1), The Answer Information (Table 2) and the Question Table (Table 3) Is there a way to popuplate at form that has all the Evaluation Information, lists each question from table 3 with the Answer Information for each of those questions from Table 2. I haven't begun creating this database yet so if there is a better way to do it i am open to suggestions. Tabl...

Sequential numbering when X column repeats records
I have a table with 2 columns Column 1 has records A A A B B C A Column 2 needs to have the number of times row one repeats a record. Something like this 1 2 3 1 2 1 Any formula that will find records on column one and sequentially number column B depending on amount of records? Thank you -RoMo -- RoMo robpiolink, Give each record a value of one with a calculated bound field in your query... GrpCounter : 1 Add GrpCounter to the report section. Group your report on each letter (in this example), and set GrpCount Running S...

Prevent user from closing form -- form event
I have 2 buttons on my form. Button1 to import the data and Button2 to upload to database. I want user to click first on button1 and then next on button2 before closing the form. What form event I should use to prevent user from closing the form with out clicking on 2nd button? Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1 You can use the Unload event. You can prevent the form closing by setting Cancel = True in the unload event. You will need a way to notify the form that uploading has finished. You can put a...

Excel 2002 parameter query returns some blanks instead of data
Hello and thanks up front... (I'm going bonkers), It's invoice time, so in my "Job Records" file, I'm using a parameter query (Excel 02, Microsoft Query) to return only those records that occur in one specified month. In the original Excel "Job Records" file on which the query is based, one column of data, labeled "PO", lists purchase order names (e.g. "VBL" for a verbal Purchase Order) or numbers (e.g. "102509"). The column is automatically formatted by Excel as "General", and I just let it ride. A second co...