Filtering Using IIF Statement (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

Try using an OR condition something like this:
IsNull(Me![RangeStart]) OR Me![RangeStart]>=[Forms]![frmSubnetsReportFilter]![txtSubnetStart]
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.

Reply | Quote

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,

Reply | Quote

I found a solution, using a criteria of >=nz([Forms]![frmSubnetsReportFilter]![txtSubnetStart],”000.000.000.000″) for the txtSubnetStart and = the value in the text box?

Thanks,

Vernon

Reply | Quote

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.

Reply | Quote

Try this as criteria:

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

Reply | Quote