Null is not null!

[MSU Sptn]

I have a multi select listbox that updates a table (TblCompliance) with the
selected emails in the list. The code for this is below:
----------------
Private Sub Cmd_SaveExit_Click()
On Error GoTo Err_Cmd_SaveExit_Click

Dim db As DAO.Database, rs As DAO.Recordset
Dim var As Variant
Dim strEmails As String

For Each var In Me.lstProgram_Manager.ItemsSelected
strEmails = strEmails & Me.lstProgram_Manager.Column(1, var) & ";"
Next var

CurrentDb.Execute "UPDATE TblCompliance SET T_Reminder_Mailing_List='" &
strEmails & "' WHERE AN_AutoNumber = " & Me!txtAN_AutoNumber
------------------
This works fine, but I also have a query set up to return all recordsets for
which the email field (T_Reminder_Mailing_List) of TblCompliance is null.
When the listbox is cleared then the Save & Exit command button is clicked
the T_Reminder_Mailing_List field in TblCompliance is blank, but the
recordset is not returned by the query as having a null
T_Reminder_Mailing_List field. Any ideas?

 

[Rick B]

What does that query look like? Post it for us. Also, is anything
returned?

Rick B

 

[MSU Sptn]

I fixed the problem by inserting "NA" when no items are selected with the
following code:
---------------------------
If lstProgram_Manager.Selected(i) = True Then
For Each var In Me.lstProgram_Manager.ItemsSelected
strEmails = strEmails & Me.lstProgram_Manager.Column(1, var) & ";"
Next var

CurrentDb.Execute "UPDATE TblCompliance SET T_Reminder_Mailing_List='" &
strEmails & "' WHERE AN_AutoNumber = " & Me!txtAN_AutoNumber

ElseIf lstProgram_Manager.Selected(i) = False Then

CurrentDb.Execute "UPDATE TblCompliance SET T_Reminder_Mailing_List='" &
"NA" & "' WHERE AN_AutoNumber = " & Me!txtAN_AutoNumber
------------------------
Then I set the query to look for where T_Reminder_Mailing_List is "NA" as
the criteria. This works, but I still don't know why it didn't work before.
Here is the SQL from the "is null" query:

SELECT TblCompliance.AN_AutoNumber, TblCompliance.T_CompAct,
TblCompliance.T_EHSProgram, TblCompliance.T_Reminder_Mailing_List
FROM TblProgram INNER JOIN TblCompliance ON TblProgram.[T_EHS Program] =
TblCompliance.T_EHSProgram
WHERE (((TblCompliance.T_Reminder_Mailing_List) Is Null));


End If

 

[Dirk Goldgar]

I have a multi select listbox that updates a table (TblCompliance)
with the selected emails in the list. The code for this is below:
----------------
Private Sub Cmd_SaveExit_Click()
On Error GoTo Err_Cmd_SaveExit_Click

Dim db As DAO.Database, rs As DAO.Recordset
Dim var As Variant
Dim strEmails As String

For Each var In Me.lstProgram_Manager.ItemsSelected
strEmails = strEmails & Me.lstProgram_Manager.Column(1, var) & ";"
Next var

CurrentDb.Execute "UPDATE TblCompliance SET
T_Reminder_Mailing_List='" & strEmails & "' WHERE AN_AutoNumber = " &
Me!txtAN_AutoNumber ------------------
This works fine, but I also have a query set up to return all
recordsets for which the email field (T_Reminder_Mailing_List) of
TblCompliance is null. When the listbox is cleared then the Save &
Exit command button is clicked the T_Reminder_Mailing_List field in
TblCompliance is blank, but the recordset is not returned by the
query as having a null T_Reminder_Mailing_List field. Any ideas?

I don't know if this is the source of your problem or not, but note that
while Null *is* Null, it is not *equal* to Null. So no join or
criterion that tests for "some field = Null" will ever return any
records.