Critera Formula and Option Group

[Carly]

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

 

[wazz]

Is Null? .......

 

[Carly]

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"

 

[Dennisk]

replace Null by ""

 

[Carly]

That doesn't work either.

 

[wazz]

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"...

 

[Carly]

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.

 

[raskew]

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

 

[wazz]

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

 

[raskew]

Hey!

It works for me!

Best Wishes,

Bob