IIf statement in Query

  • Thread starter Thread starter bymarce
  • Start date Start date
B

bymarce

I have a query as the row source for an unbound combo box so that one combo
box is filtered based on the selection of another combo box. If nothing has
been selected I want the second combo box to show all options rather than
none. I've tried using the following IIF statement in the query for the
second combo box but it doesn't work. How can I fix it? The first combo box
i Property and the second is Methods. I'm getting the error "Undefined IFF
function." Thanks for your help.
Marcie
IFF([Forms]![WorkAssignments]![Property] Is Not
Null,[Forms]![WorkAssignments]![Property], ([Properties].[Property]) Is Null
or is not null)
 
One way is like this --

IFF([Forms]![WorkAssignments]![Property] Is Not
Null,[Forms]![WorkAssignments]![Property], [Properties].[Property])

if [Properties].[Property] is the field the criteria is applied to.
 
Thanks for the idea. I wanted to do something with code but wasn't sure
where to put it.
Marcie

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you've really got to do is change the criteria of the 2nd
ComboBox's RowSource property in the AfterUpdate event procedure of the
1st ComboBox. Like this (just a proof of concept. Use your own table
and column names):

Private Sub Methods_AfterUpdate()

If IsNull(Me!cboProperty) Then
' Set up cboMethods to show all options in the drop-down list
Me!cboMethods.RowSource = "SELECT method FROM Methods"
Else
' Otherwise, set up cboMethods to show only options based on
' cboProperty.
Me!cboMethods.RowSource = "SELECT method FROM Methods WHERE " & _
"property_value = " & Me!cboProperty
End

End Sub

If the "property_value" is a Text data type then the set up string would
look like this:

"property_value = '" & Me!cboProperty & "'"

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbGmAoechKqOuFEgEQLC2wCg9D8c+qeR4uKYpxn114IuAzK0UfQAnRzt
nowS/+CuxZC9UeAaQTEvJur6
=hVOB
-----END PGP SIGNATURE-----

I have a query as the row source for an unbound combo box so that one combo
box is filtered based on the selection of another combo box. If nothing has
been selected I want the second combo box to show all options rather than
none. I've tried using the following IIF statement in the query for the
second combo box but it doesn't work. How can I fix it? The first combo box
i Property and the second is Methods. I'm getting the error "Undefined IFF
function." Thanks for your help.
Marcie
IFF([Forms]![WorkAssignments]![Property] Is Not
Null,[Forms]![WorkAssignments]![Property], ([Properties].[Property]) Is Null
or is not null)
Click to expand...
Click to expand...
 
Back
Top