Datediff in a detail line??

[Trudye]

Can you do a datdiff function in a report detail section? I entered the following statment but cannot get it to work:

IIf (nz(timecallcomplete, 0) <> 0,( DateDiff("n", TimeCallComplete, TimeDispatchWasCreated) / 60))

I also tried: DateDiff ("n", TimeCallComplete, TimeDispatchWasCreated / 60)

This also failed. In the first command I am checking for Null value before I attempt to perform the datediff.

Does anyone have any ideas?

Thanx much
Trudye

 

[mgolla]

What are you getting, an error or just incorrect data?

Is this being applied to a field on the report or is it for criteria?


HTH
Mike

 

[dhookom]

It looks like:
1) you have the later time first rather than the earlier time first
2) you don't if an "If Not" section of your IIf()
IIf (IsNull(timecallcomplete)=0, DateDiff("n", TimeCallComplete, TimeDispatchWasCreated) / 60 [blue],??? [/blue])

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Trudye]

Thanks so much for responding. Sorry it took me so long to get back to you but I got put on another project for a moment.

I am getting an invalid statement. I tried qualifying the field names (ie. tablename.fieldname) but it didn't help. I removed the nz check and the IIF. All I am asking for is a simple Datediff.

You were right I had my field order reversed in the Datediff field. But that should not have made it blow up. THe missing last parameter works fine in VB code when I do it.
Here is my latest:
DateDiff("d",dispatch.timedispatchwascreated, scheduledloads.timecallcomplete) / 60)

Thanks again
Trudye

 

[mgolla]

Your latest provides one too many parenthesis ")"

Add "(" to the beginning or remove ")" from the end after "/ 60"

HTH
Mike

 

[Trudye]

Thanks Guys I decided to do a Case function and Cast the two fields as Timestamps.

I just hope it works

Trudye

 

[dhookom]

Apparently your data isn't in Access tables? Is this an Access Report? Seems to me, this information might have some significance in finding an answer.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Trudye]

You are correct I am new to T-SQL and SQL Server. I have just been coding it 1 1/2 weeks. I am sorry for the mis-information, however I am dealing with an Access database.

Thanks to all of you,
Trudye