Put IS NULL into SQL in VBA code

[behedwin]

I have this sql statement in a query

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));

i change this statement with VBA code to this

  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

As you can see in the query sql statement there is also a WHERE statement with IS NULL
Profile_Table.ProfileArchived IS NULL

I cant figure out how to put this into my VBA code above.
How to add this to the WHERE lines.

The idea here is that IS NULL filter out cells with data in them.
So i want to do the same when i run the vba code that i use to search.
But if i just add it with an OR statement i dont think ill get the result i want.

 

[alansidman]

I use this Allen Browne designed converter.

http://allenbrowne.com/ser-71.html

 

[behedwin]

Thank you, now it works.

 

[Micron]

You can shorten something like this by assigning a control reference to a variable:

Dim strSearch As String

strSearch = Me.txtSearch.Text
....
"Where Profile_Table.Profile_ID Like '*" & strSearch & "*' " _
    & "Or FirstName Like '*" & strSearch & "*' " _ etc.

You're sure you want the Text property value of the control, and not it's value? The text property is only available when the control has the focus (otherwise you'll generate an error), plus, it is not always the same as the value property. The value property is the default property of a text box or combo, so .Value isn't even required.