nz function 2

[mespa]

Good Morning All

I have 2 (datetime) fields and 284 records are null. I need to have a value in these blank fields.

I'm reading about the NZ function, does this only apply to queries.

Would I need to make a validation rule in the table if null
report 0 , is yes HOW

Thanks in advance

 

[KenReay]

Hi

Do not see why you need Nz() for this, make an update query which setts a value into the date field, with a criteria on the date field of is Null

If you do not want to update teh atbel, then you could use Nz() in a query so

SELECT CDate(Nz(MyDate,date())) FROM MyTable ...etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mespa]

Thanks for the FYI

On this weekend, I tried that and I was trying to put a dummy date of 99/99/9999 and it returned all with a weird date od 12/30/1899
Field is defined as date/time mm/dd/yyyy

here is the SQL code I used
UPDATE Tbl_MeEval SET Tbl_MeEval.[Last Eval] = 99/99/9999, Tbl_MeEval.[Eval Due] = 99/99/9999
WHERE (((Tbl_MeEval.Last)="Alford") AND ((Tbl_MeEval.[Last Eval]) Is Null) AND ((Tbl_MeEval.[Eval Due]) Is Null));

 

[Golom]

99/99/9999" is an invalid so it defaulted to "0" which is Dec 31, 1899. If you want to default to a date it must be one that is valid like "12/31/9999". You should also enclose dates in hash marks. (e.g. #12/31/9999#)

 

[mespa]

THANK YOU SO VERY MUCH GOLOM Mission Accomplished !!!