Well, thank you very much for the detailed explanation. I like that! And again, I truly appreciate your assistance.
Doreen --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > 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/
