Show datediff summary as 0 is dates are null or < date()

[jk12]

Hi all. I have been tasked with answering the following for a database that was done by someone who has since left the company and I am approaching it from new. From what I can see, there are is a Start Date 1 End Date 1, SD2 ED2, SD3 ED3 which people use to assign blocks of days for which they will be working on a task. There is a summary of the number of days assigned using IsNull to put a 0 number of days if no dates existed and datediff to find number of days between dates if there was for each block of dates and these 3 summaries are then summed again to give a total. I.e.
SD1 1st Dec
ED1 4th Dec
SD2 8th Dec
ED2 10th Dec
SD3
ED3

Date set 1 summed to 4, Date set 2 summed to 3, Date set 3 summed to 0 as no dates entered, all summed to give 7 days assigned.

Lot of background so far but here is the problem.

It's been asked if the summary can calculate the number of assinged days excluding days that are before the current date. So the above would need to show 3 days worked as ED1 is before todays date.

I am completely stumped as to how this could be done or if it's even possible and have probably not explained myself well enough to get an answer but please be patient with me and ask for more detils if you need them.

All suggestions welcomed. Thanks

 

[Guus2005]

It's been asked if the summary can calculate the number of assinged days excluding days that are before the current date. So the above would need to show 3 days worked as ED1 is before todays date.

To make a date selection you could use

Select * From Table1
WHERE [Give Date:] Between StartDate and EndDate

Access will prompt [Give Date:]. Allowing you to enter a date.

or you can enter a date window for the startdate:

Select * From Table1
WHERE StartDate Between [Give StartDate:] and [Give EndDate:]

Access will prompt [Give StartDate:] and [Give EndDate:]

HTH