Filter with dates

[Johnny]

Hello

I'm trying to filter a form using both StartDate and EndDate, and I can
´t get the solution.

The problem is:
I have records with a Start date of operation and end date of
operation.
I need to grab all operations between Start date and End date.
No problem with operations that started and ended between the two
dates, but I have also to take in account all operations started
before start date but are still active or ended after start date or
end date,
I need to grab also operations that started after start date and
didn't ended yet or ended after end date.

I hope it´s not confuse and request your help.
Thks in advance

John

 

[Jeff Boyce]

Johnny

I'm missing something in the logic ...

How can you have a project that starts before its StartDate?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello

I'm trying to filter a form using both StartDate and EndDate, and I can
´t get the solution.

The problem is:
I have records with a Start date of operation and end date of
operation.
I need to grab all operations between Start date and End date.
No problem with operations that started and ended between the two
dates, but I have also to take in account all operations started
before start date but are still active or ended after start date or
end date,
I need to grab also operations that started after start date and
didn't ended yet or ended after end date.

I hope it´s not confuse and request your help.
Thks in advance

John

 

[Frank H]

--
Frank H
Rockford, IL

Hello

I'm trying to filter a form using both StartDate and EndDate, and I can
´t get the solution.

The problem is:
I have records with a Start date of operation and end date of
operation.
I need to grab all operations between Start date and End date.
No problem with operations that started and ended between the two
dates, but I have also to take in account all operations started
before start date but are still active or ended after start date or
end date,
I need to grab also operations that started after start date and
didn't ended yet or ended after end date.

I hope it´s not confuse and request your help.
Thks in advance

John

I don't know exactly how you have things set up, so you may need to
modify/adapt this, but the idea is:
Base the form upon a query that has calculated field(s) in it. Then place
criteria on the calculated fields, allowing you to extract the records that
you need.

I'd like to give you an example, but I can't tell what fields you might have
that would let you identify a record that "started before start date", etc.

Your calculated field(s) might have an IIF function that would compare the
"start date" against the [field where you store the info that lets you know
that it started before the start date], and if true, have it return "Early
start" or some such thing. Then you could set a criteria on that field for
"Early Start".
I hope this helps with the idea. We'd need a better understanding of your
data to be more specific.
Good Luck

 

[Johnny]

Johnny

I'm missing something in the logic ...

How can you have a project that starts before its StartDate?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello

I'm trying to filter a form using both StartDate and EndDate, and I can
´t get the solution.

The problem is:
I have records with a Start date of operation and end date of
operation.
I need to grab all operations between Start date and End date.
No problem with operations that started and ended between the two
dates, but I have also to take in account all operations started
before start date but are still active or ended after start date or
end date,
I need to grab also operations that started after start date and
didn't ended yet or ended after end date.

I hope it´s not confuse and request your help.
Thks in advance

John

Thank you Jeff for your reply, I'm going to clarify!
Project1 - Start Date 01/Apr/07, End Date 25/Dec/07
Project2 - Start Date 01/Jan/06, End Date 12/Aug/08
Project3 - Start Date 21Mar/08, End Date empty (still active)

User Input: TextBox1 - 2007 (year), TextBox2 - 2008 (year)

Result: All projects that were active between 2007 and 2008

In this case will retrieve all projects, if the requested years were
2008-2009, will give only Project2 and Project3.

I don´t know how to setup the form filter with those conditions.

John

 

[Jeff Boyce]

I'll take them one at a time ...

In your example, any project that ended before your user-inputted start year
is off the table, right? So if End-Date is < 1/1/ of
User-inputted-Start-Year, you don't want to see it.

Then, any of the remaining projects that begins after the user-inputted end
year is 'out' (Start-Date >= 1/1/ of End-Year, right?)

I may be over-simplifying, but it seems like anything left could be
considered "active" during that time frame.

Does that match your logic?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Johnny

I'm missing something in the logic ...

How can you have a project that starts before its StartDate?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello

I'm trying to filter a form using both StartDate and EndDate, and I can
´t get the solution.

The problem is:
I have records with a Start date of operation and end date of
operation.
I need to grab all operations between Start date and End date.
No problem with operations that started and ended between the two
dates, but I have also to take in account all operations started
before start date but are still active or ended after start date or
end date,
I need to grab also operations that started after start date and
didn't ended yet or ended after end date.

I hope it´s not confuse and request your help.
Thks in advance

John

Thank you Jeff for your reply, I'm going to clarify!
Project1 - Start Date 01/Apr/07, End Date 25/Dec/07
Project2 - Start Date 01/Jan/06, End Date 12/Aug/08
Project3 - Start Date 21Mar/08, End Date empty (still active)

User Input: TextBox1 - 2007 (year), TextBox2 - 2008 (year)

Result: All projects that were active between 2007 and 2008

In this case will retrieve all projects, if the requested years were
2008-2009, will give only Project2 and Project3.

I don´t know how to setup the form filter with those conditions.

John

 

[Johnny]

I'll take them one at a time ...

In your example, any project that ended before your user-inputted start year
is off the table, right?  So if End-Date is < 1/1/ of
User-inputted-Start-Year, you don't want to see it.

Then, any of the remaining projects that begins after the user-inputted end
year is 'out' (Start-Date >= 1/1/ of End-Year, right?)

I may be over-simplifying, but it seems like anything left could be
considered "active" during that time frame.

Does that match your logic?

Regards

Jeff Boyce
Microsoft Office/Access MVP










Thank you Jeff for your reply, I'm going to clarify!
Project1 - Start Date 01/Apr/07, End Date 25/Dec/07
Project2 - Start Date 01/Jan/06, End Date 12/Aug/08
Project3 - Start Date 21Mar/08, End Date empty (still active)

User Input: TextBox1 - 2007 (year), TextBox2 - 2008 (year)

Result: All projects that were active between 2007 and 2008

In this case will retrieve all projects, if the requested years were
2008-2009, will give only Project2 and Project3.

I don´t know how to setup the form filter  with those conditions.

John- Ocultar texto citado -

- Mostrar texto citado -

Hi Jeff

You openned my eyes with your last reply!

Thank you very much