Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Location
    Richmond, Virginia, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering Using IIF Statement (Access 2000)

    I'm trying to create filter to return all values if a text box is null or all values >= to the text box.

    The statement I'm using will not return any values if the text box has an entry. Any sugestions?
    Like IIf(IsNull([Forms]![frmSubnetsReportFilter]![txtSubnetStart]),"*",([Range Table].[RangeStart])>=[Forms]![frmSubnetsReportFilter]![txtSubnetStart])

    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Filtering Using IIF Statement (Access 2000)

    Try using an OR condition something like this:
    <font color=blue>IsNull(Me![RangeStart]) OR Me![RangeStart]>=[Forms]![frmSubnetsReportFilter]![txtSubnetStart]</font color=blue>
    This presumes that you have a textbox named RangeStart bound to the data source of your form - if not you will need to substitute the control name you are using. I also assumed that the control txtSubnetStart is unbound.
    It this doesn't work we will need a few more details about what you are trying to do.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering Using IIF Statement (Access 2000)

    Try this as criteria:

    [Range Table].[RangeStart]>=[Forms]![frmSubnetsReportFilter]![txtSubnetStart] OR [Forms]![frmSubnetsReportFilter]![txtSubnetStart] Is Null

  4. #4
    New Lounger
    Join Date
    Aug 2001
    Location
    Richmond, Virginia, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Using IIF Statement (Access 2000)

    Sorry, that doesn't seem to work either. Basically, what I'm trying to do is filter a report listing network equipment by any combination of network, subnet start address, subnet end address or location. This is done on a form (frmSubnetsReportFilter). For network and location, I use a combo box to generate an exact match and the results work. For subnet start address, I want the use to be able to type in, for example 246.017.000.000 and the results show all subnets with a starting address >= 246.017.000.000. The complete SQL of what I'm trying to accomplish is:

    SELECT [Networks Table].NetworkName, [Range Table].RangeStart, [Range Table].RangeEnd, "/" & Str(31-[RangeMask]) AS BitMask, [Location Data Table].LocationName, [Networks Table].NetworkID, [Location Data Table].LocationReference
    FROM ([Networks Table] INNER JOIN [Range Table] ON [Networks Table].NetworkID = [Range Table].NetworkID) INNER JOIN [Location Data Table] ON [Networks Table].SiteID = [Location Data Table].LocationReference
    WHERE ((([Range Table].RangeStart) Like IIf(IsNull([Forms]![frmSubnetsReportFilter]![txtSubnetStart]),"*",([Range Table].[RangeStart])>=[Forms]![frmSubnetsReportFilter]![txtSubnetStart])) AND (([Range Table].RangeEnd) Like IIf(IsNull([Forms]![frmSubnetsReportFilter]![txtSubnetEnd]),"*",[Forms]![frmSubnetsReportFilter]![txtSubnetEnd])) AND (([Networks Table].NetworkID) Like IIf(IsNull([Forms]![frmSubnetsReportFilter]![cboNetwork]),"*",[Forms]![frmSubnetsReportFilter]![cboNetwork])) AND (([Location Data Table].LocationReference) Like IIf(IsNull([Forms]![frmSubnetsReportFilter]![cboSite]),"*",[Forms]![frmSubnetsReportFilter]![cboSite])))
    ORDER BY [Networks Table].NetworkName, [Range Table].RangeStart;


    Thanks,

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Location
    Richmond, Virginia, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Using IIF Statement (Access 2000)

    I found a solution, using a criteria of >=nz([Forms]![frmSubnetsReportFilter]![txtSubnetStart],"000.000.000.000") for the txtSubnetStart and <=nz([Forms]![frmSubnetsReportFilter]![txtSubnetEnd],"255.255.255.255") for the txtSubnetEnd text boxes gives the results I need.

    However, out of curiosity, does anyone know if this could have been accomplished by some variation using the Like IIF(IsNull( [text box] method I had attempted earlier, where the the true result would have been "*" and the false result would have been >= the value in the text box?

    Thanks,

    Vernon

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering Using IIF Statement (Access 2000)

    The IIF function is intended for use in queries, not in forms. It's very slow. Besides, I couldn't figure out what result you were looking for if RangeStart was greater than txtSubnetStart. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •