Testing for no value in a recordset field 1

[MikeCt]

Hi

I have a table with 5 fields
Dates Date/Time
User_1 Text
User_2 Text
User_3 Text
User_4 Text

When I use the statement below, my recordset consist of every date that has a value in all four of the user fields

SELECT Dates,User_1,User_2,User_3,User_4
FROM Schedule
WHERE ((User_1<>" ") AND (User_2<>" ") AND (User_3<>" ") AND (User_4<>" "));

This statement worked fine and pull all the correct records



My question is how do I write this statement to show me every date that does not have any records in all four of the fields? Simply stated how do you test for nothing in a field?

The statement below failed to show any records, I also tried
Is Null with no better results

SELECT Dates,User_1,User_2,User_3,User_4
FROM Schedule
WHERE ((User_1=" ") AND (User_2=" ") AND (User_3=" ") AND (User_4=" "));

Thanks
Mike

 

[strongm]

Is Null should definitely work. Are you quite sure that none of the User_X fields have not got a space (or spaces) in them rather than actually being null/zero-length strings?

 

[Golom]

There's a little trick that you can use to handle NULLs, zero length strings and blank strings

WHERE     Trim$(User_1 & "")="" 
      AND Trim$(User_2 & "")="" 
      AND Trim$(User_3 & "")="" 
      AND Trim$(User_4 & "")=""