datediff function to include DateNow()

[GetReel]

Im using the date diff function to count no of days for a datein and dateout. This part is straight foward but how get it to calculate those record where the is no date out? So where there is a null in date out I need it to count it from todays date.

 

[Rich]

Iif(IsNull([DateField),DateDiff("d",[DateIn],Date(),DateDiff("d",[DateIn],[DateOut]))

 

[GetReel]

Hey Rich! Im getting a syntax error.(invalid syntax)

what is DateField?

 

[Rich]

[DateIn]

 

[GetReel]

im still getting a syntax error..Am i missing something?

DaysOccupied: Iif(IsNull([DateIn),DateDiff("d",[DateIn],Date(),DateDiff("d",[DateIn],[DateOut]))

 

[Rich]

im still getting a syntax error..Am i missing something?

DaysOccupied: Iif(IsNull([DateIn),DateDiff("d",[DateIn],Date()) ,DateDiff("d",[DateIn],[DateOut]))

OOps

 

[GetReel]

Now its telling me that the function I entered has a function containing the wrong number of arguments!

Can I kick the computer now! hehe

 

[Rich]

Copy and paste it here

 

[GetReel]

I copied and pasted Pats syntax and I still got a argument error

DaysOccupied: Iif(IsNull([DateOut), DateDiff("d", [DateIn], Date()), DateDiff("d",[DateIn],[DateOut]))

This was my original sql
SELECT tblTenant_Property_Bookings.BookingID, tblTenant_Property_Bookings.PropertyID, tblTenant_Property_Bookings.TenantID, tblTenant_Property_Bookings.DateIn, tblTenant_Property_Bookings.DateOut, DateDiff("d",[DateIn],[DateOut]) AS DaysOccupied, tblTenant_Property_Bookings.ExpectedExitDate, tblTenant_Property_Bookings.[4WeekLetter] FROM tblTenant_Property_Bookings ORDER BY tblTenant_Property_Bookings.DateOut;

 

[GetReel]

Ive found the problem..there was a missing bracket
Iif(IsNull([DateOut),
]
Thanks for your help guys!!!!!!!!!!!!!!!!!!!!!!
awesome stuff!!