If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"IIF" in Criterea area of Query
I've created a Microsoft Access2000 form in which users can select several
parameters for including or excluding data on a report All brokerage firms or an individual brokerage firm can be selected, all regions or an individual region can be selected and all reps or an individual rep can be selected. (Eg., the user would select an individual region from a Combo Box or he would Check a Box (A Check Box) called "AllReps" When the user selects any combination of these parameters, on the Form a hidden Hold Text Box for Brokerage Firm, Region and Rep will contain either Null (for selecting All values) or the actual value selected for Brokerage Firm, Region or Rep . (These hold fields are scrutinized in the report query) The user will then press a command button to produce the report. I want to have, ideally, just one query underlying this report but I'm having difficulty with the criteria for selecting or excluding fields. On the Criterea (Where condition) line of the Query Design view I've used a conditional IIF for the fields like this one: IIf([Forms]![Reports-Custom]![HoldRep]=Null,Not "ZZZ",[Forms]![Reports- Custom]![HoldRep]) The idea of the "Not 'ZZZ' is that it would be a condition that would always be True and hence all the Reps would be selected, but it's not working when the HoldRep text box on the form is Null . It only works when there is a real Rep code moved to that HoldRep text box on the form. It seems that an actual value must present instead of what I have. Any suggestions? Thanks, John |
#2
|
|||
|
|||
"IIF" in Criterea area of Query
If I understand your problem, rather than using an IIF statement, how
about making your criteria: Like [Forms]![Reports-Custom]![HoldRep] &"*" This would result in all Reps being selected. |
#3
|
|||
|
|||
"IIF" in Criterea area of Query
IT WORKED!!!
Thanks, HanSolo! And Thanks to this wonderful site where knowledge is freely shared! JohnG HanSolo wrote: If I understand your problem, rather than using an IIF statement, how about making your criteria: Like [Forms]![Reports-Custom]![HoldRep] &"*" This would result in all Reps being selected. |
#4
|
|||
|
|||
"IIF" in Criterea area of Query
[Forms]![Reports-Custom]![HoldRep]=Null
will always return False (or an error). Null never equates to anything, (not even Null), so the statement will never be True. IsNull([Forms]![Reports-Custom]![HoldRep]) should at least point your Iif() to the expected T/F response. (Whether "Not 'ZZZ'" executes as expected is a separate issue...) HTH, -- George Nicholson Remove 'Junk' from return address. "JohnG" u17584@uwe wrote in message news:5a43eca738722@uwe... I've created a Microsoft Access2000 form in which users can select several parameters for including or excluding data on a report All brokerage firms or an individual brokerage firm can be selected, all regions or an individual region can be selected and all reps or an individual rep can be selected. (Eg., the user would select an individual region from a Combo Box or he would Check a Box (A Check Box) called "AllReps" When the user selects any combination of these parameters, on the Form a hidden Hold Text Box for Brokerage Firm, Region and Rep will contain either Null (for selecting All values) or the actual value selected for Brokerage Firm, Region or Rep . (These hold fields are scrutinized in the report query) The user will then press a command button to produce the report. I want to have, ideally, just one query underlying this report but I'm having difficulty with the criteria for selecting or excluding fields. On the Criterea (Where condition) line of the Query Design view I've used a conditional IIF for the fields like this one: IIf([Forms]![Reports-Custom]![HoldRep]=Null,Not "ZZZ",[Forms]![Reports- Custom]![HoldRep]) The idea of the "Not 'ZZZ' is that it would be a condition that would always be True and hence all the Reps would be selected, but it's not working when the HoldRep text box on the form is Null . It only works when there is a real Rep code moved to that HoldRep text box on the form. It seems that an actual value must present instead of what I have. Any suggestions? Thanks, John |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Advanced SQL Query | Wes Henry | General Discussion | 3 | December 14th, 2005 09:55 PM |
Moving from xBase/Clipper | [email protected] | New Users | 1 | February 3rd, 2005 07:25 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM | |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |