How to add IS NULL to sql statement? (1 Viewer)

behedwin

New member
Local time
Today, 16:54
Joined
Nov 15, 2017
Messages
9
Hi

I need some help

I have this query statement
Code:
SELECT Profile_Table.Profile_ID, Profile_Table.FirstName, Profile_Table.LastName, Profile_Table.Personnummer, Profile_Table.AnstalldSom, Profile_Table.JobbarPa, Profile_Table.WorkStartDate, Profile_Table.WorkEndDate, Profile_Table.JobbProcent, Profile_Table.AnstallningBelastar, Profile_Table.Mandag, Profile_Table.Tisdag, Profile_Table.Onsdag, Profile_Table.Torsdag, Profile_Table.Fredag, Profile_Table.Address1, Profile_Table.Address2, Profile_Table.ZipCode, Profile_Table.City, Profile_Table.Phone1, Profile_Table.Phone2, Profile_Table.Phone3, Profile_Table.Email1, Profile_Table.Email2, Profile_Table.Anstallningsform, Profile_Table.IKT_Ansvarig, Profile_Table.IT_Ansvarig, Profile_Table.Skyddsombud, Profile_Table.SBA_Ansvarig, Profile_Table.Forstelarare, Profile_Table.Arbetslagsledare, Profile_Table.Belastningsregistret, Profile_Table.Tystnadsplikt, Profile_Table.Aktuellt_Avtal, [firstname] & " " & [lastname] AS Fullname, Profile_Table.ProfileArchived FROM Profile_Table WHERE (((Profile_Table.ProfileArchived) Is Null));
It removes all records that are not null in the column profilearchived.

Then the user can search within the records.
So i change the sql to this with the help of VBA using this code.

Code:
  Dim strSource As String

  strSource = "SELECT Profile_Table.Profile_ID, Profile_Table.FirstName, Profile_Table.LastName, Profile_Table.Personnummer, Profile_Table.AnstalldSom, Profile_Table.JobbarPa, Profile_Table.WorkEndDate, Profile_Table.JobbProcent " & _
    "FROM Profile_Table " & _
    "Where Profile_Table.Profile_ID Like '*" & Me.txtSearch.Text & "*' " _
    & "or FirstName Like '*" & Me.txtSearch.Text & "*' " _
    & "or LastName Like '*" & Me.txtSearch.Text & "*' " _
    & "or Personnummer Like '*" & Me.txtSearch.Text & "*' " _
    & "or Anstalldsom Like '*" & Me.txtSearch.Text & "*' " _
    & "or JobbProcent Like '*" & Me.txtSearch.Text & "*' " _
    & "or Anstallningsform Like '*" & Me.txtSearch.Text & "*' " _
    & "or JobbarPa Like '*" & Me.txtSearch.Text & "*' "
      Me.ListPicker.RowSource = strSource
      Me.ListPicker.Requery

This also works really great.



However the problem i have now is that when the user do a search... the user search in the records i filtered out with the "IS NULL" statement in the first sql code.

How do i add the IS NULL to the second sql code?

Any ideas?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:54
Joined
Aug 30, 2003
Messages
36,118
Are you wanting that all the other criteria are applied only to records where that field is Null? If so:

Code:
  strSource = "SELECT Profile_Table.Profile_ID, Profile_Table.FirstName, Profile_Table.LastName, Profile_Table.Personnummer, Profile_Table.AnstalldSom, Profile_Table.JobbarPa, Profile_Table.WorkEndDate, Profile_Table.JobbProcent " & _
    "FROM Profile_Table " & _
    "Where (Profile_Table.Profile_ID Like '*" & Me.txtSearch.Text & "*' " _
    & "or FirstName Like '*" & Me.txtSearch.Text & "*' " _
    & "or LastName Like '*" & Me.txtSearch.Text & "*' " _
    & "or Personnummer Like '*" & Me.txtSearch.Text & "*' " _
    & "or Anstalldsom Like '*" & Me.txtSearch.Text & "*' " _
    & "or JobbProcent Like '*" & Me.txtSearch.Text & "*' " _
    & "or Anstallningsform Like '*" & Me.txtSearch.Text & "*' " _
    & "or JobbarPa Like '*" & Me.txtSearch.Text & "*') AND ProfileArchived) Is Null  "
 

behedwin

New member
Local time
Today, 16:54
Joined
Nov 15, 2017
Messages
9
Wow. that worked perfectly.

thank you very much.

did not know i could put a section into () and then add a AND statement.
very nice.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:54
Joined
Aug 30, 2003
Messages
36,118
Happy to help! Hopefully you fixed the copy/paste typo I just noticed.
 

Users who are viewing this thread

Top Bottom