Query with IIF and Null values

Teun Ham

Board Regular
Joined
Mar 1, 2005
Messages
88
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)?
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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)
 
Upvote 0
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.

Code:
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
 
Upvote 0
Thanks Kreszch68, so far it seems to work!

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

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top