Subtracting between two dates for total days

i have three columns for Date In, Date Out, Total Days. If in Date In: 8/1/2011 and Date Out: 8/12/2011 Then the Total Days would be: 11 days. If Date Out is empty then Total Days = Current Date - Date In.

Problem: i cout get the total days if Date out is empty and using current date just fine, but getting total between date out and date in is giving me an error.

In the textbox in the reportviewer i have this expression:

=IIf(Fields!DateOut.Value=" "," ",Fields!TotalDa开发者_如何转开发ys.Value)

The TotalDays is Current Date - Date In which i calculate in a stored procedure and just return the results.

I was thing of doing this but still i am getting an #Error in the textbox if i tried subtracting between date out and in if they are not empty.

=IIf(Fields!DateOut.Value=" ",DateDiff("d",Fields!DateOut.Value,Fields!DateIn.Value),Fields!TotalDays.Value)

Any suggestions.... Thanks

I know this is a little late, but i came up with the same issue. Here's what I did:

=CDate(Fields!DateOut.Value).Subtract(Fields!DateIn.Value).Days

Hope this helps!

Thanks for the info. Just wanted to share as I am not a programmer and this might be helpful to someone who is in my shoes that is required to write code.

I was able to create my formula from Answer 1. I had a requirement to have the Period End Date display the date 6 days prior. IE. EndDate = 06/24/2012 Field needs to display as 06/18/2012

=(CDate(Fields!EndDate.Value).AddDays(-6))