Am I using the Right Code (1 Viewer)

[Theman]

I have two fields one is called RequestCompletionDate and the other is called DupDateDist. I am trying to create a third field in my query called Days Pending which would capture the difference in days between the date in the RequestCompletionDate and the empty value in DupDateDist. This would tell me how long it is taking the product to get distributed from the completion date. So if the RequestCompletionDate field has 7/31/01 as a value and there is no value in DupDateDist, then the Days Pending value should be 3 if I ran the report today (8/2/01). I used the datediff like this in my query field:
"Days Pending: DateDiff("d",[DupDateDist],[requestcompletiondate])

However, the Days Pending won't display any value. What am I missing here? Thanks.

Dan

 

[Jack Cowley]

You have to have a date for the DupDateDist. If you want to calculate it from the date you run the query then use this :

Days Pending: DateDiff("d",Date,[requestcompletiondate])

'Date' is the current system date so it will always be the date of the day you run the query.

[This message has been edited by Jack Cowley (edited 08-02-2001).]

 

[Rich]

I dont want to step on your toes here Jack but Theman might be looking for the days when there is a date in DupDateDist in which case use Iif (IsNull([DupDateDist]),DateDiff("d",[requestcompletiondate],Date()),DateDiff("d",[requestcompletiondate],[DupDateDist]))
HTH

 

[Theman]

Rich and Jack,

Both your solutions worked great as I do have several different reports I need to generate using both codes. Thanks much!!!

Dan