Date Calculator in Report Expression

[ashleyhickman]

I apologize in advance - I have almost no working knowledge of access. I have to use it at work and have been tasked with creating an expression in a report to run a date calculation.

I use an Access report to list all the projects I have going on, and each one has a checkbox that I check if it's been proofed and is ready to go. I've created a date calculator at the top of the report that will calculate the number of days between today and the furthest due date. I am trying to change the date calculator to calculate the number of days between today and the furthest due date IF and only if my checkbox is checked showing that is it completed. Specifically, I only want the expression to pull entries from projects that have "yes" in the "Proofed" field.

This is my date calculator: =DateDiff("d",Date(),Max([EffectiveDate]))

This will calculate the days between today and the last due date of my projects, but I need it to only from entries that have "yes" in a certain field. Any suggestions on how to do this? I am probably doing this in a very convoluted way, using a report instead of a query or so on.



Thanks!

[pbaldy]

Try

=DateDiff("d",Date(),DMax("[EffectiveDate]", "SourceQueryName", "Proofed = True"))

where SourceQueryName is the name of the query the report is based on.

[ashleyhickman]

That worked! Thank you!!

[pbaldy]

Happy to help and welcome to the site by the way!