Date Dilemmas

sTrongFuse

Registered User.
Local time
Today, 07:31
Joined
Dec 3, 2012
Messages
26
I have an A2007 database with a table called ComplaintsData which contains two date fields [Complaint Received] and [Complaint Closed]

I have a little search form called "ParamForm" which has two unbound text boxes "StartDate" and "EndDate" which, when used in conjunction with queries allows me to build reports for complaints received between two dates, and complaints closed between two dates.

I put the code: Between [forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate] in the criteria field of [Complaint Recieved] for my recieved report query and in [Complaint Closed] for my closed report query.

All works, so far so good.

Now I'm trying to go a stage further. I need to code the following.

1. Where [Complaint Closed] is between "Start Date" and End Date" but is also less than or equal to [Recieved Date] + 28 days.

2. Where [Complaint Closed] is between "Start Date" and End Date" but is also greater than [Recieved Date]+ 28 days and less than or equal to [Recieved Date] + 56 days.

3. Where [Complaint Closed] is between "Start Date" and End Date" but is also greater than [Recieved Date] + 56 days.

I realise this probably requires 3 separate queries as I want to build a report for each of these scenarios.

Any takers...?

T
 
You can add other criteria for the dates. I usually switch from the design grid view to SQL view and add the criteria there. You will need to utilize the dateadd() function

1.Where [Complaint Closed] is between "Start Date" and End Date" but is also less than or equal to [Recieved Date] + 28 days

The SQL text would look like this:

WHERE [Complaint Closed] Between [forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate] AND [Complaint Closed]<=dateadd("d",28,[Received Date])

2. Where [Complaint Closed] is between "Start Date" and End Date" but is also greater than [Recieved Date]+ 28 days and less than or equal to [Recieved Date] + 56 days.

WHERE [Complaint Closed] Between [forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate] AND [Complaint Closed] BETWEEN dateadd("d",28,[Received Date]) AND dateadd("d",56,[Received Date])

3. Where [Complaint Closed] is between "Start Date" and End Date" but is also greater than [Recieved Date] + 56 days.


WHERE [Complaint Closed] Between [forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate] AND [Complaint Closed] > dateadd("d",56,[Received Date])
 
Worked a treat. Thanks a lot.

T

You can add other criteria for the dates. I usually switch from the design grid view to SQL view and add the criteria there. You will need to utilize the dateadd() function

1.Where [Complaint Closed] is between "Start Date" and End Date" but is also less than or equal to [Recieved Date] + 28 days

The SQL text would look like this:

WHERE [Complaint Closed] Between [forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate] AND [Complaint Closed]<=dateadd("d",28,[Received Date])

2. Where [Complaint Closed] is between "Start Date" and End Date" but is also greater than [Recieved Date]+ 28 days and less than or equal to [Recieved Date] + 56 days.

WHERE [Complaint Closed] Between [forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate] AND [Complaint Closed] BETWEEN dateadd("d",28,[Received Date]) AND dateadd("d",56,[Received Date])

3. Where [Complaint Closed] is between "Start Date" and End Date" but is also greater than [Recieved Date] + 56 days.


WHERE [Complaint Closed] Between [forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate] AND [Complaint Closed] > dateadd("d",56,[Received Date])
 

Users who are viewing this thread

Back
Top Bottom