Calculating Date Field

[homeguard]

i put another column on a query to calculate the number of dates between dates. here is what i have:

[Date]-[CompletedTime]+1

but if the record does not have a completed time then it will be blank, is there a way to say:

if [CompletedTime] = 0 then
[Date] - Date()
else
[Date]-[CompletedTime]+1
end if

anyone know how to do this?

 

[PaulBricker]

Difference:IIf([CompletedTime] = 0, [Date] - Date(),[Date]-[CompletedTime]+1)


A couple things. It's better not to use "Reserved words like Date as a field name. Enclosing it in brackets [] will help, but better to name the field something like
fldDate. Access will like that better.

Also, if there is no value in CompletedTime, the value might actually be Null and not 0 depending on how you set it up. YOu would have to test for a null differently than a 0.

Paul

 

[homeguard]

thank you sir.