Combinding Datediff and iIF functions in a form

[oggstar]

I have two variables, date lodged and date determined. I am simply trying to establish the difference in number of days between the dates entered in a form. This is to be displayed in the form under the field name “number of days”.

There are two scenarios to determine the difference between the two dates and the number of days.

1. Number of days = Date determined - date lodged
=DateDiff("d",[date lodged],[Date determined])

[Date lodged] Date lodged 1-9-01=
[date determined] date Determined =5-9-01
[Number of days] Number of days= Access query to determine the number of days. Result = 4

2. If date determined is blank, Number of days = Todays date -Date lodged
=DateDiff("d",[Date lodged],Now())

[Date lodged] Date lodged 1-9-01=
[date determined] date Determined = No date entered blank
[Number of days] Number of days= Access query to determine the number of days based on today’s date 9-9-01. Result = 8

Now I have both of these above function to work the problem is when I try and combine both formulas. I thought I would use the iif function. If date determined is blank the sum equals the date determined - todays date, otherwise it equals date determined - date lodged.

Thus I tried the formula below , however had no luck with it, it appears to be a syntax error, any suggestions greatly appreciated.

=IIf(IsNull(Number of days), =DateDiff("d",[Date lodged],Now()), =DateDiff("d",[date lodged],[Date determined]))

 

[ToeShot]

I modified your statement to the one below if that don't work the statement may be to complex (if it is an a query) But from what i can tell you are using it on a form.
You do not need the "=" sign in front of the datediff's statement because you one or the other will replace the IIF

=IIf(IsNull(Number of days), DateDiff("d",[Date lodged],Now()), DateDiff("d",[date lodged],[Date determined]))