Is this Q complicated?

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

This is a table where I have to query data from.
# DateRecodEntered RecordStatus StatusDate

1 08/09/03 Updated 08/30/03
2 09/02/03 New
3 09/05/03 Updated 09/10/03
4 09/10/03 Closed 09/20/03
5 09/10/03 Closed 10/02/03

Explanation of entry:
Record Nr.1 was entered on 08/09/03 and updated on
08/30/03.and so on.
I want to retrieve:
Record entered between [Enter start DateRecordEntered]And
[Enter End DateRecordEntered], but exclude those records
where status is Closed and StatusDate falls between the
date user have selected on the criteria.

Let's assume the user select date for September. He will
retrieve record Number 2,3 and 5.
He won't get record Nr.4, because it was closed on Sep.20,
but he will get record Nr.5, because it was closed on Oct,
but entered on Sep.
 
Dear Bob:

I would venture that the query itself is not difficult, but the logic
involved requires a bit of the right kind of thought.

SELECT *
FROM YourTable
WHERE DateRecordEntered BETWEEN [Enter start DateRecordEntered]
AND [Enter End DateRecordEntered]
AND (Updated <> "Closed"
OR Status Date NOT BETWEEN [Enter start DateRecordEntered]
AND [Enter End DateRecordEntered])

Does this do what you want?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thank you for your quick respond.
I think I was not clear on my first message. I will try to
explain below.

First select: Look on Date created field. Retrieve records
entered between [Enter start DateRecordEntered]And [Enter
End DateRecordEntered](e.g for September, 4 records meet
this createria)

Second select: Look on StatusDate for Closed Claims. If
status date for closed claims between [Enter start
DateRecordEntered]And [Enter End DateRecordEntered]don't
select that record .(Don't select record Nr.5)
If StatusDate for Closed Claims greater
than [Enter start DateRecordEntered]And [Enter End
DateRecordEntered], select that record.(Select rec. Nr.6)
The report from this query (which I don't know if it is
possible to write) will have rec. Nr.3,4 and 6.

Can I retrieve these records in one query and create the
report?
I will appreciate any thoughts.

-----Original Message-----
Dear Bob:

I would venture that the query itself is not difficult, but the logic
involved requires a bit of the right kind of thought.

SELECT *
FROM YourTable
WHERE DateRecordEntered BETWEEN [Enter start DateRecordEntered]
AND [Enter End DateRecordEntered]
AND (Updated <> "Closed"
OR Status Date NOT BETWEEN [Enter start DateRecordEntered]
AND [Enter End DateRecordEntered])

Does this do what you want?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

This is a table where I have to query data from.
# DateRecodEntered RecordStatus StatusDate

1 08/09/03 Updated 08/30/03
2 09/02/03 New
3 09/05/03 Updated 09/10/03
4 09/10/03 Closed 09/20/03
5 09/10/03 Closed 10/02/03

Explanation of entry:
Record Nr.1 was entered on 08/09/03 and updated on
08/30/03.and so on.
I want to retrieve:
Record entered between [Enter start DateRecordEntered]And
[Enter End DateRecordEntered], but exclude those records
where status is Closed and StatusDate falls between the
date user have selected on the criteria.

Let's assume the user select date for September. He will
retrieve record Number 2,3 and 5.
He won't get record Nr.4, because it was closed on Sep.20,
but he will get record Nr.5, because it was closed on Oct,
but entered on Sep.
Click to expand...

.
Click to expand...
 
Back
Top