Date() formula problems with DateDiff in Access

[Dave]

I tried usin the Date() in an access dateDiff formula, but the field just
comes up blank.

IIf(Field1="",DateDiff("d",Field2,Date()),DateDiff("d",Field3,Field4))

Field3 and Field4 actually come up with the number of days different, but
Date() and Field2 just show up blank. Could someone please tell me how to
correct this so that the Dat() and Field2 show up as a number?

 

[KARL DEWEY]

Try doing it a little at a time --
Try this alone and see if you get results -- DateDiff("d",Field2,Date())

Try this --
IIf(Field1 Is Null,DateDiff("d",Field2,Date()),DateDiff("d",Field3,Field4))

Build a little, test a little.

 

[Jeff Boyce]

Dave

In Access, Null propogates. You might see this as "blank". Can you confirm
that the Date() function is working properly? (add it as a new field in a
query and see what it returns)

Can you confirm that NO rows have a Null in [Field2]? If you have
(legitimate) Nulls in [Field2], you'll need to modify your query to exclude
those rows with Null in [Field2].

Regards

Jeff Boyce
Microsoft Office/Access MVP