Query with IIF and Null values

[Teun Ham]

To all,

I am struggling with a query, but I can't get my head around it.

I have a database with a table which shows what items our employees have in their possession.
There are also a lot of items which do NOT have any owner, so those are not visible in that table (that's logical)
I have made a query (Q_AssetPossession) which shows ALL the items with their current owner (so that could be me, somebody else or nobody (Null))

So far so good...

I have made a form with a checkbox. The checkbox is labeled "My possessions only".
If that checkbox is checked, I want to show only the items owned by a user.
If that checkbox is unchecked, I want to show ALL the items (owned by that user, other users and owned by nobody...so basicly the query I have made).

I can get the checked state (only show the items owned by the current user), but I can't get the unchecked-state (show all the items, including the items which have no owner)

I have tried all sorts of things, like

Field: EmpID
Table: Q_AssetPossession
Criteria: Like IIf([Forms]![T_Emp]![chkBezit]=-1;[Forms]![T_Emp]![EmpId];"*")
(Works for the first part, but it does not show the Null values)

or

Field: EmpID
Table: Q_AssetPossession
Criteria: IIf([Forms]![T_Emp]![chkBezit]=-1;[Forms]![T_Emp]![EmpId];Is Null Or Is Not Null)
(IIF and Is Null do not work together )

or

Expr1: IIf(Forms!T_Emp!chkBezit=-1;Q_AssetPossession.EmpID;Nz(Q_AssetPossession.EmpID;Forms!T_Emp!EmpId))
Criteria: [Forms]![T_Emp]![EmpId]
(For some odd reason the criteria for the expression is erased when I close (and save) the query)

So how can I get ALL the [Q_AssetPossession].[EmpID] (both Null and Not Null)?

 

[Teun Ham]

I am getting closer...

This expression seems to work:

Owner: Q_AssetPossession.EmpID & ' '
Criteria: Like IIf([Forms]![T_Emp]![chkBezit]=-1;[Forms]![T_Emp]![EmpId] & ' ';"*")

The addition of & ' ' seems to make the IFF compatible with the Null values.

BUT EVERYTIME I CLOSE AND REOPEN MY QUERY, THE CRITERIA HAS BEEN ERASED!!! (Sorry for being so loud, but Access is driving me crazy)

 

[Kreszch68]

Supposing you have the EmpID on your form, and having set Q_AssetPossession as the recordsource for your form, you better use the next approach.
To make it work you'll have to strip all criteria from Q_AssetPossession.

The next code is triggered in the after update event of the checkbox 'chkBezit'. It creates a new SQL statement that is then used as the recordsource for your form.

Private Sub chkBezit_AfterUpdate()
Dim sSQL As String
Dim sWhereClause As String
Select Case Me.chkBezit.Value
        Case 0 'Not selected, show ALL
           sWhereClause = vbNullString
        Case -1 'Selected, Use EmpID
           sWhereClause = "Where EmpID = '" & Me.empID & "'" 'Use this line if EmpID is a string
         '  sWhereClause = "Where EmpID = " & Me.empID 'Use this line if EmpID is a number
End Select
sSQL = "Select * From Q_AssetPossession " & sWhereClause
Me.RecordSource = sSQL
End Sub

 

[Teun Ham]

Thanks, I will give it a spin

 

[Teun Ham]

Thanks Kreszch68, so far it seems to work!

(The actual SQL is a bit more complicated, but it works!)