need help with finding records that are Empty or Blank 2

[DougP]

I have project numbers with a Service and sometimes the Service is missing. Example
Project Service
080345 ASN

If I use Is not null it still returns a few records with an empty Service even though technically its not null there is nothing I can see anyway. Someone got in the box and then deleted it ???
So I need to find only records if the Service is Empty or Blank. Since Is Not Null does not seem to work in all cases.
this code gets most of them but it I look in the table there are several that are empty, blank or whatever they need something in them. I need to know or other things don't work right.

UPDATE [TimeSheet Details] SET [TimeSheet Details].[Number] = "1"
WHERE ((([TimeSheet Details].Number) Is Null) AND (([TimeSheet Details].[Project Number]) Is Not Null));

TIA

DougP

 

[PWise]

UPDATE [TimeSheet Details] SET [TimeSheet Details].[Number] = "1"
WHERE [TimeSheet Details].Number Is Null AND nz([TimeSheet Details].[Project Number],""="";

 

[PWise]

sorry

UPDATE [TimeSheet Details] SET [TimeSheet Details].[Number] = "1"
WHERE [TimeSheet Details].Number Is Null AND nz([TimeSheet Details].[Project Number],"")="";

 

[PHV]

To catch null, space only or ZeroLength:
WHERE Trim(yourField & "") = ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

both of them work , so double stars!!!!

Pwise, I had to add a "NOT" in like so:
UPDATE [TimeSheet Details] SET [TimeSheet Details].[Number] = "1"
WHERE [TimeSheet Details].Number Is NOT Null AND nz([TimeSheet Details].[Project Number],"")="";

Otherwise Thanks a bunch.
PHV, your Access knowledge is Immense. I’ve been using Access for 15 years since Ver 1.1 in 1992. But I don’t know half of what you help me with. I’m glad you monitor this site.

DougP