Any activity between 2 dates

[Sue]

I have 2 fields in my table - start date for a project and end date for a
project. (There are multiple projects.) I want to design a parameter query
that will let me know if there's any activity for a project during a given
time period. In other words:
1. List any projects that opened during this time period.
2. List any projects that REMAINED open throughout this time period.
3. List any projects that closed during this time period.

Can it be done?
Can I then generate a report that spits out only those projects satisfying
one of the above criteria?

Thank you so much.

 

[Sue]

Woah - you're over my head here!
I've read the post you're referring to on the Access Monster board, but not
sure how to make it do what I need.
I'm not using any time's here - it's date-only info.
The table for source is titled tblProtocol. The primary key is an
auto-number field, ProtocolID. The date fields are DateProtocolOpened and
DateProtocolClosed; each of those has a mm/dd/yyyy format.
Can you help me further? I'd sure be grateful!

 

[John Spencer]

To get Projects that started during a specific period.
FieldateProtocolOpened
Criteria: Between [Period Start Date] and [Period End Date]

To get projects that Closed during the period
Field: DateProtocolClosed
Criteria: Between [Period Start Date] and [Period End Date]

To get projects that were active during the period
Field:FieldateProtocolOpened
Criteria: <= [Period End Date]

Field: DateProtocolClosed
Criteria: >= [Period Start Date]

IF you are using a form to get the parameter input and calling the
report from the form, you can accomplish what you want using a bit of VBA

Don't apply any date range criteria in the query that underlies the report.

On a form, add to text boxes for the dates. (txtStart and txtEnd)

Add an option group with three choices. (I'll call it fWhichReport
1 - Opened
2 - Actice
3 - Closed

Add a button to the form (I'll call it btnPrintReport)

In vba for the button's click property add code that looks something
like the following.

Dim strCriteria as String
Dim strFormat as String
strFormat = "#yyyy-mm-dd#"

Select Case fWhichReport
Case 1
strCriteria = "DateProtocolOpened Between " & _
strFormat(Me.txtStart) & " AND " & strFormat(Me.txtEnd)
Case 2
strCriteria = "DateProtocolOpened <= " & _
strFormat(Me.txtEnd) & _
" AND DateProtocolClosed >=" & _strFormat(Me.txtStart)
Case 3
strCriteria = "DateProtocolClosed Between " & _
strFormat(Me.txtStart) & " AND " & strFormat(Me.txtEnd)
End Select

If Len(StrCriteria) > 0 then
Docmd.OpenReport "Name of Report as String",acViewPreview,,strCriteria
End if

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Sue]

Generally several years. Highly variable, but never less than 2 years or so.
--
Thanks for your time!

Sue -

How long do these projects last?

Bob

Woah - you're over my head here!
I've read the post you're referring to on the Access Monster board, but not
sure how to make it do what I need.
I'm not using any time's here - it's date-only info.
The table for source is titled tblProtocol. The primary key is an
auto-number field, ProtocolID. The date fields are DateProtocolOpened and
DateProtocolClosed; each of those has a mm/dd/yyyy format.
Can you help me further? I'd sure be grateful!

Hi Sue -

[quoted text clipped - 18 lines]

Thank you so much.