IsNull is a function that tests a single paramter and returns True if the parameter contains the Null value. IS NULL is a phrase in SQL. If you switch to SQL View in your version, you'll find:
WHERE ((DataEntry.RepID) = IIf(IsNull([Forms]![ReportForm].[RepID]),[DataEntry].[RepID], [Forms]![ReportForm].[RepID]) IIf performs the test in the first argument and returns the second argument if True or the third argument if false. If RepID on the form is null, the resulting test is: WHERE DataEntry.RepID = DataEntry.RepID .. which works as long as RepID never contains a Null. If RepID on the form is not null, the test is: WHERE DataEntry.RepID = [Forms]![ReportForm]![RepID] If you use my method, you'll find the SQL looks something like: WHERE ((DataEntry.RepID) = [Forms]![ReportForm]![RepID]) Or ([Forms]![ReportForm]![RepID] IS NULL) Only one of a set of OR predicates needs to be true to select the row, so if the control on the form IS NULl, all rows are selected regardless of what is in RepID. If the control on the form does not contain a null, then RepID in each row must match what's in the control. I could have also said: [Forms]![ReportForm]![RepID] Or (IsNull([Forms]![ReportForm]![RepID])) .. but the native IS NULL test in SQL is faster than calling the function. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of rokkeee Sent: Thursday, January 12, 2006 11:45 AM To: [email protected] Subject: [ms_access] Re: Multi-option form for reports Okay, this may sound dumb, but I seem to remember the function "Is Null" preceding the control, and without a space in between the two words. My format seems to still work...was that format for earlier versions of Access? --- In [email protected], "rokkeee" <[EMAIL PROTECTED]> wrote: > > Yes, John, I think it IS time for me to learn basic coding and I > will check out your website and download a database to get me > started [again]. > Thanks so much, John, you're great! > Doreen > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > Doreen- > > > > That should work just fine for fields that cannot be Null. If you > need to > > filter on a field that might not have a value, you need to use > this on the > > criteria line: > > > > [Forms]![ReportForm].[RepID] Or ([Forms]![ReportForm].[RepID] IS > NULL) > > > > But don't you think it's time you learned how to basic coding? > You can find > > examples of custom "filter by form" on my website in the databases > in the > > Downloads area. > > > > John Viescas, author > > "Building Microsoft Access Applications" > > "Microsoft Office Access 2003 Inside Out" > > "Running Microsoft Access 2000" > > "SQL Queries for Mere Mortals" > > http://www.viescas.com/ > > > > > > -----Original Message----- > > From: [email protected] [mailto:[EMAIL PROTECTED] > On Behalf > > Of rokkeee > > Sent: Thursday, January 12, 2006 10:14 AM > > To: [email protected] > > Subject: [ms_access] Re: Multi-option form for reports > > > > John, > > My problem is that I don't know "code"...I actually have figured > out > > a way to do it within a query that has IIF statements as the > > criteria for each field, such as: > > IIf(IsNull([Forms]![ReportForm].[RepID]),[DataEntry].[RepID], > [Forms]! > > [ReportForm].[RepID]) > > So far it seems to work unless you can help me write some code. > > > > Thanks for your help! > > Doreen > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > wrote: > > > > > > Doreen- > > > > > > The give the user maximum flexibility, you should use reports > with > > > unfiltered recordsets and dynamically build the "WHERE" clause > in > > code > > > behind the form. Use the filter you build in code in the > > WhereCondition > > > parameter of the OpenReport method. Examine each option and > > include a > > > filter for it only if the user has picked or entered a value. > > > > > > John Viescas, author > > > "Building Microsoft Access Applications" > > > "Microsoft Office Access 2003 Inside Out" > > > "Running Microsoft Access 2000" > > > "SQL Queries for Mere Mortals" > > > http://www.viescas.com/ > > > > > > > > > -----Original Message----- > > > From: [email protected] > [mailto:[EMAIL PROTECTED] > > On Behalf > > > Of rokkeee > > > Sent: Thursday, January 12, 2006 9:41 AM > > > To: [email protected] > > > Subject: [ms_access] Multi-option form for reports > > > > > > Within my database, I know the different options we'll want to > use > > for > > > reporting. I've created an unbound form with several "options" > > fields > > > (combo boxes) and have each field linked to an individual query > > right > > > now. But, how can I set up the 'reality' that we will choose > more > > than > > > one option at a time? For example, we would choose a RepID and > a > > > Product at the same time. > > > > > > Thanks, > > > Doreen > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
