How to add IS NULL to sql statement?

[behedwin]

Hi

I need some help

I have this query statement

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.

  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]

Are you wanting that all the other criteria are applied only to records where that field is Null? If so:

  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]

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]

Happy to help! Hopefully you fixed the copy/paste typo I just noticed.

 

[behedwin]

Happy to help! Hopefully you fixed the copy/paste typo I just noticed.

yeah i saw it.