Date Range

[Marie]

Hi,

I have a database that has a list of files that are stored away in numbered
boxes. There is only one table which lists the box number, the contents of
the box, and the date range (for example BoxNumber: 12345; Contents: Payroll
records; From: 1/1/98; To: 12/31/98. The user needs to locate a box by
either entering in the items wanted, for which I created a query using Like
"*" & [Enter Item Searching For] & "*". The problem is, how can they search
for a stored box based on a date range from 1/1/98 to 12/31/98? Right now,
the Start Date (1/1/98) and End Date (12/31/98) are in separate fields. Do I
need to have the whole date range in the same field such as 1/1/98-12/31/98?
If so, how can you add 2 dates in the same field? I would appreciate any
help I can get. Thanks.

Marie

 

[Rick B]

I don't think this can be done easily. The best I could come up with is to
use your existing criteria and also include criteria to pull all the records
where the year matches an entry. For example...


Add a new column to your query with your contents as...
YearFrom: Format([Start Date],"yy")
and your criteria of:
=[Enter Year as XX]

Then add another column as...
YearTo: Format([End Date],"yy")
and your criteria of:
=[Enter Year as XX]


You would need to put the criteria above on two separate lines to create an
"OR" condition. Your contents criteria would need to be on both of these
lines. In short, you'd look for all boxes where the contents matched AND
the start date contained the year entered OR the contents matched AND the
end ate contained the year entered.

The user would then have a short list to view.

Hopefully someone will have some other ideas. I know how to use "between"
in the criteria, but don't know how to go the other way around.

Good Luck,

Rck B

 

[John Vinson]

Hi,

I have a database that has a list of files that are stored away in numbered
boxes. There is only one table which lists the box number, the contents of
the box, and the date range (for example BoxNumber: 12345; Contents: Payroll
records; From: 1/1/98; To: 12/31/98. The user needs to locate a box by
either entering in the items wanted, for which I created a query using Like
"*" & [Enter Item Searching For] & "*". The problem is, how can they search
for a stored box based on a date range from 1/1/98 to 12/31/98? Right now,
the Start Date (1/1/98) and End Date (12/31/98) are in separate fields. Do I
need to have the whole date range in the same field such as 1/1/98-12/31/98?
If so, how can you add 2 dates in the same field? I would appreciate any
help I can get. Thanks.

Marie

Basically you want to find those records where the date range supplied
by the user overlaps the date range stored in the table, right? They
must be separate fields; storing two dates in a Date/Time field is
impossible, and storing two dates in a text field makes them useless.
Try a criterion on both From and To of

BETWEEN [Enter start date:] AND [Enter end date:]

Put these criteria on *SEPARATE* lines of the query grid, and put the
[Enter item searching for] criterion on both lines.


John W. Vinson[MVP]

 

[Marie]

Thank you very much for your help. These suggestions worked for me.

Marie