Access Reports Date If then Calculation

[jobrien]

On a monthly report, I am trying to flag the projects where the status has changed from the previous month by placing a * next to the status. I have tried the following in a text box:

=IIf(DateDiff("d",[SPECIAL ASSESSMENT DISTRICTS]![DateModified],Date())<31,"*"," ")


I am calculating the difference in dates between the current date and the date the record was last modified. If less than 31 days, return the *. If greater than 31 days, return nothing. However, I keep getting an #Err in the print preview of the report.

Any suggestions are welcome.

[Rabbit]

What's [SPECIAL ASSESSMENT DISTRICTS]? Access will have no idea what this is. If it's on the report then you don't need it. If it's on a different object then you need [Forms or Reports]!SPECIAL ASSESSMENT DISTRICTS]![DateModified].

[jobrien]

What's [SPECIAL ASSESSMENT DISTRICTS]? Access will have no idea what this is. If it's on the report then you don't need it. If it's on a different object then you need [Forms or Reports]!SPECIAL ASSESSMENT DISTRICTS]![DateModified].

[Special Assessment Districts] is the name of the table under which Date Modified is stored. should I reference it from the form from where it would have been entered?

[Rabbit]

[Special Assessment Districts] is the name of the table under which Date Modified is stored. should I reference it from the form from where it would have been entered?

You can't reference anything that's closed using that method. You can't reference tables and queries at all using that method. You can use the D functions, DLookup in particular. However, it seems like you should be including date modified in the query that the report is based on; that way you won't need to reference anything. You can pull it directly from the report's record source.