I have got an option group on a form with 4 radio buttons.

I have then got a query which contains a few fields one of which has a criteria formula as follows:

IIf([Forms]![frm:salesinfo]![frame31]=1,Null,IIf([Forms]![frm:salesinfo]![frame31]=2,"01",IIf([Forms]![frm:salesinfo]![frame31]=3,"02","03")))

Please could anyone explain how I get the 1st part to work...I need option 1 to run as if there was no criteria at all...

Hope this makes sense

Kind Regards

Carly

Is Null? .......

When I change it to Is Null I get the following message:

"This expression is type incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

replace Null by ""

That doesn't work either.

looks like "" should work. (i did it a bit differently but "" worked).
you might need to add .Value ([Forms]![frm:salesinfo]![frame31].Value).
i actually had trouble with the last bit. not sure how you got the third part to work without using "Or"...

You say you did it differently & "" worked, please could you let me know how you did it...my formula is like this:

IIf([Forms]![frm:salesinfo]![frame31].[value]=1,"",IIf([Forms]![frm:salesinfo]![frame31].[value]=2,"01",IIf([Forms]![frm:salesinfo]![frame31].[value]=3,"02","03")))

but it just brings back a blank result.

This formula works when I select options 2, 3 & 4 but not 1...When option 1 is selected I would like it to work like there is no critera formula at all, i.e. Bring through all 01, 02 & 03 values.

Hi -

Here's a working example, with three options, that I use to determine what items to display. You should be able to modify it to your needs:

Private Sub optSelectedBy_AfterUpdate()
Dim strSQL As String

strSQL = "Select qryTblDevProcess.* from qryTblDevProcess "

Select Case optSelectedBy
Case 1 'all
Case 2 'complete
strSQL = strSQL & "WHERE Done = True"
Case 3 'incomplete
strSQL = strSQL & "WHERE Done = False"
End Select

strSQL = strSQL & ";"
Me.RecordSource = strSQL
Me![Item].ForeColor = IIf(Me![Done], 0, 255)
Me![Remarks].ForeColor = IIf(Me![Done], 0, 255)

Me.Requery

End Sub

HTH - Bob

hi. bob beat me to it but i just worked up a quick one without knowing that... so i'll post it anyway. it's basically the same thing, but "the longer version." you can easily change it to use one strSql variable but i'll just leave it. hope you don't mind bob.
edit: on second thought, the redundant variable is bugging me so i've changed the code...now they're nearly identical...

Dim strSql As String
Dim strFilterCriteria As String

strSql = "SELECT * From YourTable"

Select Case Forms!YourForm.YourFrame.Value
Case 1
strFilterCriteria = ""
Case 2
strFilterCriteria = " YourField = 'x' "
Case 3
strFilterCriteria = _
" YourField = 'y' Or YourField = 'z' "
End Select

If strFilterCriteria = "" Then
Me.RecordSource = strSql & ";"
Else
Me.RecordSource = strSql & " WHERE " & strFilterCriteria & ";"
End If

Hey!

It works for me!

Best Wishes,

Bob